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

# 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)

```

## 参照情報
* [PL/pgSQLを使ってみよう](https://qiita.com/ester41/items/3401606322888a0a7f21)
* [PostgreSQL PL/pgSQL — SQL手続き言語](https://www.postgresql.jp/document/14/html/plpgsql.html)
* [PL/pgSQLで戻り値でテーブルを使う時の注意点](http://aoyagikouhei.blog8.fc2.com/blog-entry-126.html)