Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@etaka

PostgreSQL PL/pgSQLで関数、プロシージャを作成する

Posted at

PL/pgSQLで関数、プロシージャを作成する

PL/pgSQLで関数、プロシージャを作成するときについて、気にすることをまとめてみました

PL/pgSQL関数の書式

CREATE [OR REPLACE] FUNCTION 関数名 ([引数][,引数, ...]) [RETURNS 戻り値型 | RETURNS TABLE ] AS $$
DECLARE	
BEGIN	
END;
$$ LANGUAGE plpgsql

関数 テンプレート

引数なし、戻り値がvoid型のテンプレートのテンプレート

CREATE OR REPLACE FUNCTION func_plpgsql() RETURNS void AS $$
DECLARE	
BEGIN
END; 
$$ LANGUAGE plpgsql;

関数の作成

例1 Hello World を返すPL/pgSQL関数。

CREATE OR REPLACE FUNCTION HelloWorld_plpgsql() RETURNS text AS $$
DECLARE	
BEGIN	
 return 'Hello World';
END; 
$$ LANGUAGE plpgsql;

作成例

例1の実施

testdb=# select helloworld_plpgsql();
 helloworld_plpgsql 
--------------------
 Hello World
(1 row)

例2-1 引数を返す PL/pgSQL関数

CREATE OR REPLACE FUNCTION echo_plpgsql(message text) RETURNS text AS $$
DECLARE 
BEGIN   
 return message;
END; 
$$ LANGUAGE plpgsql;

例2-1の実施

testdb=# select echo_plpgsql('Hello World');
 echo_plpgsql 
--------------
 Hello World
(1 row)

例2-2 引数を返す PL/pgSQL関数

引数が型のみの記述の場合、$1, $2, ... で表現します

CREATE OR REPLACE FUNCTION echo2_plpgsql(text) RETURNS text AS $$
DECLARE 
BEGIN   
 return $1;
END; 
$$ LANGUAGE plpgsql;

例2-2の実施

testdb5=# select echo2_plpgsql('Hello World');
 echo2_plpgsql 
---------------
 Hello World
(1 row)

例3 prod テーブルの prod_name の最初の行を返す

CREATE OR REPLACE FUNCTION prod_name_plpgsql() RETURNS text AS $$
DECLARE
 name text; 
BEGIN
 SELECT prod_name INTO name FROM prod;
 return name;
END; 
$$ LANGUAGE plpgsql;

例3の実施

testdb=# select prod_name_plpgsql();
 prod_name_plpgsql 
-------------------
 みかん
(1 row)

例4-1 戻り値が集合の場合

RETURN QUERY を使って返す

CREATE OR REPLACE FUNCTION prod_name2_plpgsql() RETURNS TABLE(r_prod_name text) AS $$
DECLARE
BEGIN
 RETURN QUERY SELECT prod_name FROM prod;
END;
$$ LANGUAGE plpgsql;

例4-1の実施

testdb5=# select prod_name2_plpgsql();
 prod_name2_plpgsql 
--------------------
 みかん
 りんご
 メロン
 かき
 みかん
 みかん
 すもも
(7 rows)

例4-2 戻り値の項目の名前と関数本体のSQL文の項目名が一致していると、関数を実施したときにエラーになります。

CREATE OR REPLACE FUNCTION prod_name2_plpgsql() RETURNS TABLE(prod_name text) AS $$
DECLARE
BEGIN
 RETURN QUERY SELECT prod_name FROM prod;
END;
$$ LANGUAGE plpgsql;

例4-2の実施

testdb=# CREATE OR REPLACE FUNCTION prod_name2_plpgsql() RETURNS TABLE(prod_name text) AS $$
DECLARE
BEGIN
 RETURN QUERY SELECT prod_name FROM prod;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
testdb=# select prod_name2_plpgsql()
ERROR:  column reference "prod_name" is ambiguous
LINE 1: SELECT prod_name FROM prod
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT prod_name FROM prod
CONTEXT:  PL/pgSQL function prod_name2_plpgsql() line 4 at RETURN QUERY
testdb=#

例4-3 戻り値が集合の場合

戻り値に SETOF テーブル名 を 使用した場合

CREATE OR REPLACE FUNCTION prodlists_plpgsql() RETURNS SETOF prod AS $$
DECLARE
BEGIN
 RETURN QUERY SELECT * FROM prod;
END;
$$ LANGUAGE plpgsql;

実施例

testdb5=# select prodlists_plpgsql();
 prodlists_plpgsql 
-------------------
 (1," みかん",50)
 (2," りんご",70)
 (3," メロン",100)
 (4,かき,100)
 (5,みかん,10)
 (5,みかん,10)
 (1,すもも,500)
(7 rows)

testdb5=# select * from prodlists_plpgsql();
 prod_id | prod_name | price 
---------+-----------+-------
       1 |  みかん   |    50
       2 |  りんご   |    70
       3 |  メロン   |   100
       4 | かき      |   100
       5 | みかん    |    10
       5 | みかん    |    10
       1 | すもも    |   500
(7 rows)

例5 戻り値がvoid型のPL/pgSQL関数

RETURN文がなくてもエラーになりません

CREATE OR REPLACE FUNCTION testdataProd_plpgsql() RETURNS void AS $$
DECLARE
BEGIN
 INSERT INTO prod VALUES(1, 'みかん', 10);
END;
$$ LANGUAGE plpgsql;

例5の実施例

testdb=# select testdataProd_plpgsql();
 testdataprod_plpgsql 
----------------------
 
(1 row)

例6 戻り値の型がvoid以外の場合

RETURN 文がないと、実施時にエラーになります。

CREATE OR REPLACE FUNCTION testdataProd2_plpgsql() RETURNS text AS $$
DECLARE
BEGIN
 INSERT INTO prod VALUES(1, 'みかん', 10);
END;
$$ LANGUAGE plpgsql;

例6の実施例

testdb=# select testdataProd2_plpgsql();
ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function testdataprod2_plpgsql()

例7 RETURNING句付きのInsert文でSQL関数を作成

RETURNINGの後に記載する項目名と戻り値のRETURNS TABLEの後に記載する項目名は同じにならないようにします

CREATE OR REPLACE FUNCTION testdataProd3_plpgsql() RETURNS TABLE(r_prod_id integer, r_prod_name text, r_price numeric) AS $$
DECLARE
BEGIN
 RETURN QUERY INSERT INTO prod VALUES(1, 'みかん', 10) RETURNING prod_id, prod_name, price;
END;
$$ LANGUAGE plpgsql;

例8 関数等の呼び出しで関数の結果を無視したい場合は、SELECT文の代わりにPERFORM文を使用します

CREATE OR REPLACE FUNCTION insertdata_plpgsql() RETURNS void AS $$
DECLARE
BEGIN

INSERT INTO orders(order_id,order_date,customer_id,prod_id,qty) VALUES (1,clock_timestamp(),1,1,10);
PERFORM pg_sleep(1);
INSERT INTO orders(order_id,order_date,customer_id,prod_id,qty) VALUES (2,clock_timestamp(),2,2,5);
PERFORM pg_sleep(1);
INSERT INTO orders(order_id,order_date,customer_id,prod_id,qty) VALUES (3,clock_timestamp(),3,3,8);
PERFORM pg_sleep(1);
INSERT INTO orders(order_id,order_date,customer_id,prod_id,qty) VALUES (4,clock_timestamp(),2,1,3);
PERFORM pg_sleep(1);
INSERT INTO orders(order_id,order_date,customer_id,prod_id,qty) VALUES (5,clock_timestamp(),3,2,4);

END;
$$ LANGUAGE plpgsql;

例8の実施例

testdb5=# select * from orders;
 order_id | order_date | customer_id | prod_id | qty 
----------+------------+-------------+---------+-----
(0 rows)

testdb5=# select insertdata_plpgsql();
 insertdata_plpgsql 
--------------------
 
(1 row)

testdb5=# select * from orders;
 order_id |         order_date         | customer_id | prod_id | qty 
----------+----------------------------+-------------+---------+-----
        1 | 2024-11-19 07:25:40.166706 |           1 |       1 |  10
        2 | 2024-11-19 07:25:41.168681 |           2 |       2 |   5
        3 | 2024-11-19 07:25:42.170404 |           3 |       3 |   8
        4 | 2024-11-19 07:25:43.172036 |           2 |       1 |   3
        5 | 2024-11-19 07:25:44.173624 |           3 |       2 |   4
(5 rows)

PL/pgSQLでプロシージャの作成

PL/pgSQLプロシージャの書式

PL/pgSQLでプロシージャを作成する場合は、書式は以下になります

CREATE [OR REPLACE] PROCEDURE プロシージャ名 ([引数][,引数, ...])  LANGUAGE plpgsql AS $$
DECLARE	
BEGIN	
END;
$$

PL/pgSQLプロシージャ テンプレート

引数なしのテンプレート

CREATE OR REPLACE PROCEDURE proc()
LANGUAGE plpgsql
AS $$
DECLARE	
BEGIN	
END;
$$;

作成例

例

CREATE OR REPLACE PROCEDURE insertProd_plpgsql(prod_name text, price numeric)
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
INSERT INTO prod VALUES (nextval('seqno'), prod_name, price);
END;
$$;

例の実施

testdb5=# select * from prod;
 prod_id | prod_name | price 
---------+-----------+-------
       1 |  みかん   |    50
       2 |  りんご   |    70
       3 |  メロン   |   100
       4 | かき      |   100
       5 | みかん    |    10
       5 | みかん    |    10
       1 | すもも    |   500
(7 rows)

testdb5=# call insertProd_plpgsql('柿', 200);
CALL
testdb5=# select * from prod;
 prod_id | prod_name | price 
---------+-----------+-------
       1 |  みかん   |    50
       2 |  りんご   |    70
       3 |  メロン   |   100
       4 | かき      |   100
       5 | みかん    |    10
       5 | みかん    |    10
       1 | すもも    |   500
       2 | 柿        |   200
(8 rows)

参照情報

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?