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)

参照情報