PostgreSQL 問い合わせ言語(SQL)で関数、プロシージャを作成する

問い合わせ言語(SQL)で関数、プロシージャを作成するときについて、気にすることをまとめてみました

## 関数の定義

関数は CREATE [OR REPLACE] FUNCTION SQLコマンドで作成し、書式は以問い合わせ言語(SQL)下です

書式
```
CREATE [OR REPLACE] FUNCTION 関数名 ([引数][,引数, ...]) [RETURNS 戻り値型 | RETURNS TABLE ] AS '関数定義文字列' LANGUAGE 言語名

引数
[引数モード] [引数名] 引数型 [ { DEFAULT | = } デフォルト値]

言語名
関数を実装している言語の名前を指定します。 このパラメータには、sql、c、internal、もしくはユーザ定義手続き言語(例：plpgsql)の名前を指定可能です。

関数定義文字列
関数の本体の定義を記述した文字列定数です。言語名で指定した言語で記述します。<br>
定義を記述する際は、通常の単一引用符ではなく、ドル引用符 を使用するほうが便利に記載できます。
```

問い合わせ言語(SQL)で関数を作成する場合は、書式は以下です

問い合わせ言語(SQL)関数 書式
```
CREATE [OR REPLACE] FUNCTION 関数名 ([引数][,引数, ...]) [RETURNS 戻り値型 | RETURNS TABLE ] AS $$
関数定義文字列
$$ LANGUAGE SQL
```

関数本体でのコメントの記述方法は以下の通りです
* 二重のダッシュ(--)はその行末までがコメントになります
* /* はコメントブロックの始まりを意味し、次に  */が現れるまでをコメントとします


### 問い合わせ言語(SQL)関数テンプレート

引数なし、戻り値が void 型
```
CREATE OR REPLACE FUNCTION func_name() RETURNS void AS $$

$$ LANGUAGE SQL;
```

### 関数本体の書き方

SQL関数は、任意のSQL文のリストを実行し、そのリストの最後の問い合わせの結果を返します。 <br>単純な（集合ではない）場合、最後の問い合わせの結果の最初の行が返されます。 最後の問い合わせが何も行を返さない時はNULL値が返されます。<br>SQL関数の本体は、セミコロンで区切ったSQL文のリストで記述します。 最後の文の後のセミコロンは省略可能です。 関数がvoid型と宣言されていない限り、最後の文はSELECT、またはRETURNING句を持つINSERT、UPDATE、またはDELETEでなければなりません。

例1 Hello World を返すSQL関数。

最後のSELECT文の結果が戻り値になります
```
CREATE OR REPLACE FUNCTION HelloWorld() RETURNS text AS $$
 SELECT 'Hello World';
$$ LANGUAGE SQL;
```

戻り値は、’Hello' になります 
```
CREATE OR REPLACE FUNCTION Hello() RETURNS text AS $$
 SELECT 'Hello World';
 SELECT 'Hello'
$$ LANGUAGE SQL;
```

例1の実施例
```
testdb5=# select HelloWorld();
 helloworld  
-------------
 Hello World
(1 row)

testdb5=# select Hello();
 hello 
-------
 Hello
(1 row)
```

例2-1 引数を返す SQL関数
```
CREATE OR REPLACE FUNCTION echo (message text) RETURNS text AS $$
  SELECT message;
$$ LANGUAGE SQL;
```

例2-1の実施
```
testdb5=# select echo('Hello');
 echo  
-------
 Hello
(1 row)
```

例2-2 引数を返す SQL関数<br>
引数が型のみの記述の場合、`$1, $2,` ... で表現します
```
CREATE OR REPLACE FUNCTION echo2 (text) RETURNS text AS $$
  SELECT $1
$$ LANGUAGE SQL;
```
例2-2 の実施例
```
testdb5=# select echo2('Hello');
 echo2 
-------
 Hello
(1 row)
```

例2-3 引数が複合型の場合
```
CREATE OR REPLACE FUNCTION echo_prod(prod) RETURNS SETOF prod AS $$
  SELECT $1
$$ LANGUAGE SQL;
```

例2-3の実施<br>
複合型をリテラル定数として記述するには、フィールド値をカンマで区切り、それらを括弧で括ります 
```
testdb5=# select echo_prod('(4, "かき", 200)');
    echo_prod    
-----------------
 (4," かき",200)
(1 row)
```

例2-4 引数が複合型の場合 その2<br>
複合型の属性(要素)には、argname.fieldnameや$1.fieldnameのようなドット表記を用いて、アクセスします
```
CREATE OR REPLACE FUNCTION echo_prod_name(prod) RETURNS text AS $$
  SELECT $1.prod_name
$$ LANGUAGE SQL;
```

例2-4の実施
```
testdb5=# select echo_prod_name('(4, "かき", 200)');
 echo_prod_name 
----------------
  かき
(1 row)
```

例3 prod テーブルの prod_name の最初の行を返すSQL関数
```
CREATE OR REPLACE FUNCTION prod_name() RETURNS text AS $$
 SELECT prod_name FROM prod;
$$ LANGUAGE SQL;
```

例3の実施例 テーブルの最初の行が戻り値として返します
```
testdb5=# testdb5=# select prod_name from prod;
 prod_name 
-----------
  みかん
  りんご
  メロン
(3 rows)

testdb5=# select prod_name();
 prod_name 
-----------
  みかん
(1 row)
```

例4 戻り値の型と SELECT文の返す型が一致しないと関数登録時にエラーになります
```
testdb=# CREATE OR REPLACE FUNCTION prod_name2() RETURNS text AS $$
 SELECT * FROM prod;
$$ LANGUAGE SQL;
ERROR:  return type mismatch in function declared to return text
DETAIL:  Final statement must return exactly one column.
CONTEXT:  SQL function "prod_name2"
testdb=# 
```

例5-1 戻り値が集合の場合の定義<br>
戻り値にTABLEを使用した場合
```
CREATE OR REPLACE FUNCTION prod_name2() RETURNS TABLE(prod_name text) AS $$
 SELECT prod_name FROM prod;
$$ LANGUAGE SQL;
```

例5-1の実施例
```
testdb5=# select prod_name2();
 prod_name2 
------------
  みかん
  りんご
  メロン
(3 rows)
```

例5-2 戻り値が集合の場合の定義<br>
戻り値に SETOF テーブル名 を 使用した場合
```
CREATE OR REPLACE FUNCTION prodlists() RETURNS SETOF prod AS $$
 SELECT * FROM prod;
$$ LANGUAGE SQL;
```

例5-2 の実施例
```
testdb5=# select prodlists();
     prodlists     
-------------------
 (1," みかん",50)
 (2," りんご",70)
 (3," メロン",100)
 (4,かき,100)
 (5,みかん,10)
 (5,みかん,10)
(6 rows)

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

例6 戻り値がvoid型のSQL関数<br> 
戻り値を返さなくていいので、結果を返さない、通常のよく使われるINSERT文等の構文が使えます
```
CREATE OR REPLACE FUNCTION testdataProd() RETURNS void AS $$
 INSERT INTO prod VALUES(4, 'かき', 100);
$$ LANGUAGE SQL;
```

例5の実施例
```
testdb5=# select * from prod;
 prod_id | prod_name | price 
---------+-----------+-------
       1 |  みかん   |    50
       2 |  りんご   |    70
       3 |  メロン   |   100
(3 rows)

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

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


例6 RETURNING句付きのInsert文でSQL関数を作成
```
CREATE OR REPLACE FUNCTION testdataProd3() RETURNS TABLE(prod_id integer, prod_name text, price numeric) AS $$
 INSERT INTO prod VALUES(5, 'みかん', 10) RETURNING prod_id, prod_name, price;
$$ LANGUAGE SQL;
```

例6の実施例
```
testdb=# select testdataprod3();
 testdataprod3 
---------------
 (1,みかん,10)
(1 row)

testdb5=# select * from testdataProd3();
 prod_id | prod_name | price 
---------+-----------+-------
       5 | みかん    |    10
(1 row)
```

### 問い合わせ言語(SQL)プロシージャの作成

書式
```
CREATE [OR REPLACE] PROCEDURE プロシージャ名 ([引数][,引数, ...]) LANGUAGE 言語名 AS '定義文字列' 

引数
[引数モード] [引数名] 引数型 [ { DEFAULT | = } デフォルト値]

言語名
プロシージャを実装している言語の名前を指定します。 このパラメータには、sql、c、internal、もしくはユーザ定義手続き言語(例：plpgsql)の名前を指定可能です。

定義文字列
プロシージャの本体の定義を記述した文字列定数です。言語名で指定した言語で記述します。
定義を記述する際は、通常の単一引用符ではなく、ドル引用符 を使用するほうが便利に記載できます。
```

問い合わせ言語(SQL)でプロシージャを作成する場合は、書式は以下になります

問い合わせ言語(SQL)プロシージャ 書式
```
CREATE [OR REPLACE] PROCEDURE プロシージャ名 ([引数][,引数, ...])  LANGUAGE SQL AS $$
定義文字列
$$ 
```

### 問い合わせ言語(SQL)プロシージャ テンプレート

引数なしのテンプレート
```
CREATE OR REPLACE PROCEDURE proc()
LANGUAGE SQL
AS $$

$$;
```

例1 問い合わせ言語(SQL)でプロシージャの作成
```
CREATE OR REPLACE PROCEDURE insertProd(prod_name text, price numeric)
LANGUAGE SQL
AS $$
INSERT INTO prod VALUES (nextval('seqno'), prod_name, price);
$$;
```

例1 の実施例
```
testdb5=# testdb5=# call insertProd('すもも', 500);
CALL
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)
```

### 参照情報
* [PostgreSQL 問い合わせ言語（SQL）関数](https://www.postgresql.jp/document/14/html/xfunc-sql.html)
* [PostgreSQLのReturning句について](https://qiita.com/jun99stu0209/items/5d97d6333bc12e1320f5)
* [PostgreSQL CREATE PROCEDURE](https://www.postgresql.jp/document/14/html/sql-createprocedure.html)
* [PostgreSQL ユーザ定義プロシージャ](https://www.postgresql.jp/document/14/html/xproc.html)