PostgreSQLでユーザー定義関数、ユーザー定義プロシージャーを作成する


## ユーザー定義関数の作成

PostgreSQLは4種類の関数を作成することが出来ます。

* 問い合わせ言語関数（SQLで作成された関数）
* 手続型言語関数（PL/pgSQLやPL/Tclなどで作成された関数）
* 内部関数
* C言語関数

関数は CREATE [OR REPLACE] FUNCTION SQLコマンドで作成します。　

書式
```
CREATE [OR REPLACE] FUNCTION 関数

関数
関数名 ([引数][,引数, ...]) [RETURNS 戻り値型 | RETURNS TABLE ] AS '関数定義文字列' LANGUAGE 言語名

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

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

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

関数の作成例
```
CREATE OR REPLACE FUNCTION HelloWorld() RETURNS text AS $$
 SELECT 'Hello World' AS result;
$$ LANGUAGE SQL;
```

## 関数の実行
SELECT 文で実行することが出来ます

```
SELECT 関数名(引数);
SELECT * FROM 関数名(引数);
```

実施例1 SELECT 関数名
```
testdb=# SELECT echo('Hello World');
    echo     
-------------
 Hello World
(1 row)
```

実施例2 SELECT * FROM 関数名
```
testdb=# select * from echo('Hello World');
    echo     
-------------
 Hello World
(1 row)
```

実施例3 引数に列名を指定
```
testdb=# select *, echo(prod_name) from prod;
 prod_id | prod_name  | price |    echo    
---------+------------+-------+------------
       1 | みかん     |   100 | みかん
       2 | りんご     |   100 | りんご
       3 | メロン     |   100 | メロン
       4 | バナナ     |   100 | バナナ
       5 | もも       |   100 | もも
       6 | なし       |   100 | なし
       7 | さくらんぼ |   100 | さくらんぼ
       8 | スイカ     |   100 | スイカ
       9 | 夏みかん   |   100 | 夏みかん
       1 | みかん     |    10 | みかん
       1 | みかん     |    10 | みかん
       1 | みかん     |    10 | みかん
       1 | みかん     |    10 | みかん
(13 rows)
```

## 関数の削除
DROP FUNCTION SQLコマンドで、関数を削除します。

書式
```
DROP FUNCTION [ IF EXISTS ] 関数名 [(引数)] 
```

関数の削除の実施例
```
testdb=# \df
                           List of functions
 Schema |     Name     | Result data type | Argument data types | Type 
--------+--------------+------------------+---------------------+------
 public | echo         | text             | name text           | func
 public | echosql      | text             | name text           | func
 public | helloworld   | text             |                     | func
 public | prod_name    | text             |                     | func
 public | testdataprod | void             |                     | func
(5 rows)

testdb=# DROP FUNCTION echo;
DROP FUNCTION

testdb=# \df
                           List of functions
 Schema |     Name     | Result data type | Argument data types | Type 
--------+--------------+------------------+---------------------+------
 public | echosql      | text             | name text           | func
 public | helloworld   | text             |                     | func
 public | prod_name    | text             |                     | func
 public | testdataprod | void             |                     | func
(4 rows)
 
```

## プロシージャの作成
CREATE PROCEDURE SQLコマンドでプロシージャを作成します

書式
```
CREATE [OR REPLACE] PROCEDURE プロシージャ

プロシージャ
プロシージャ名 ([引数][,引数, ...]) LANGUAGE 言語名 AS '定義文字列' 

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

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

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

プロシージャ作成の実施例
```
CREATE PROCEDURE delete_prod()
LANGUAGE SQL
AS $$
DELETE FROM prod WHERE price = 10;
$$;
```

## プロシージャの実行
CALL コマンドでプロシージャを実行します

書式
```
CALL プロシージャ([引数])
```

プロシージャ実行の実施例
```
testdb=# select * from prod;
 prod_id | prod_name  | price 
---------+------------+-------
       1 | みかん     |   100
       2 | りんご     |   100
       3 | メロン     |   100
       4 | バナナ     |   100
       5 | もも       |   100
       6 | なし       |   100
       7 | さくらんぼ |   100
       8 | スイカ     |   100
       9 | 夏みかん   |   100
       1 | みかん     |    10
       1 | みかん     |    10
       1 | みかん     |    10
       1 | みかん     |    10
(13 rows)

testdb=# call delete_prod();
CALL

testdb=# select * from prod;
 prod_id | prod_name  | price 
---------+------------+-------
       1 | みかん     |   100
       2 | りんご     |   100
       3 | メロン     |   100
       4 | バナナ     |   100
       5 | もも       |   100
       6 | なし       |   100
       7 | さくらんぼ |   100
       8 | スイカ     |   100
       9 | 夏みかん   |   100
(9 rows)
```

## プロシージャの削除
DROP PROCEDURE SQLコマンドを使用して、プロシージャを削除します

書式
```
DROP PROCEDURE [ IF EXISTS ] プロシージャ名 [ (引数) ]
```

実施例
```
testdb=# SELECT nspname,proname,rolname,lanname,prokind FROM pg_proc, pg_namespace, pg_language,pg_authid  where nspname = 'public' and pg_namespace.oid = pronamespace and pg_language.oid = prolang and pg_authid.oid = proowner;
 nspname |   proname    | rolname  | lanname | prokind 
---------+--------------+----------+---------+---------
 public  | echosql      | postgres | sql     | f
 public  | helloworld   | postgres | sql     | f
 public  | testdataprod | postgres | sql     | f
 public  | prod_name    | postgres | sql     | f
 public  | delete_prod  | postgres | sql     | p
(5 rows)

testdb=# DROP PROCEDURE delete_prod;
DROP PROCEDURE
testdb=# SELECT nspname,proname,rolname,lanname,prokind FROM pg_proc, pg_namespace, pg_language,pg_authid  where nspname = 'public' and pg_namespace.oid = pronamespace and pg_language.oid = prolang and pg_authid.oid = proowner;
 nspname |   proname    | rolname  | lanname | prokind 
---------+--------------+----------+---------+---------
 public  | echosql      | postgres | sql     | f
 public  | helloworld   | postgres | sql     | f
 public  | testdataprod | postgres | sql     | f
 public  | prod_name    | postgres | sql     | f
(4 rows)
```

## ユーザー定義関数、ユーザー定義プロジージャの一覧を表示

### psql &nbsp; \df メタコマンド
psqlの \df メタコマンドで作成した関数、プロシージャの一覧を表示させることが出来ます

```
testdb=# \df
                           List of functions
 Schema |     Name     | Result data type | Argument data types | Type 
--------+--------------+------------------+---------------------+------
 public | delete_prod  |                  |                     | proc
 public | echosql      | text             | name text           | func
 public | helloworld   | text             |                     | func
 public | prod_name    | text             |                     | func
 public | testdataprod | void             |                     | func
(5 rows)
```

psql \df+ 関数名またはプロシージャ名 メタコマンドで関数、プロシージャのソースコードを含む詳細を表示できます。
```
testdb-# \df+ echosql
                                                                                   List of functions
 Schema |  Name   | Result data type | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language |       Source code        | Description 
--------+---------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+--------------------------+-------------
 public | echosql | text             | name text           | func | volatile   | unsafe   | postgres | invoker  |                   | sql      |                         +| 
        |         |                  |                     |      |            |          |          |          |                   |          |   SELECT name AS result;+| 
        |         |                  |                     |      |            |          |          |          |                   |          |                          | 
(1 row)
```

### pg_proc カタログ
pg_procカタログは関数、プロシージャ、集約関数あるいはWINDOW関数に関する情報が格納されています。

自身が定義した関数は、名前空間が'public'に格納されています。

pg_procカタログから作成した関数・プロシージャの名前、作成した言語、所有者、関数・プロシージャの種類を表示するSQL文
```
SELECT nspname, proname, rolname, lanname, prokind FROM pg_proc, pg_namespace, pg_language, pg_authid  where nspname = 'public' and pg_namespace.oid = pronamespace and pg_language.oid = prolang and pg_authid.oid = proowner;
```

実施例
```
testdb=# SELECT nspname,proname,rolname,lanname,prokind FROM pg_proc, pg_namespace, pg_language, pg_authid  where nspname = 'public' and pg_namespace.oid = pronamespace and pg_language.oid = prolang and pg_authid.oid = proowner;
 nspname |   proname    | rolname  | lanname | prokind 
---------+--------------+----------+---------+---------
 public  | echosql      | postgres | sql     | f
 public  | helloworld   | postgres | sql     | f
 public  | testdataprod | postgres | sql     | f
 public  | prod_name  　| postgres | sql     | f
 public  | delete_prod  | postgres | sql     | p
(5 rows)
```

関数・プロシージャのソースコードは、pg_procカタログのprosrcに格納されています。
```
testdb=# SELECT nspname,proname,prosrc,rolname,lanname, prokind FROM pg_proc, pg_namespace, pg_language,pg_authid  where nspname = 'public' and pg_namespace.oid = pronamespace and pg_language.oid = prolang and pg_authid.oid = proowner;
 nspname |   proname    |                   prosrc                   | rolname  | lanname | prokind 
---------+--------------+--------------------------------------------+----------+---------+---------
 public  | echosql      |                                           +| postgres | sql     | f
         |              |   SELECT name AS result;                  +|          |         | 
         |              |                                            |          |         | 
 public  | helloworld   |                                           +| postgres | sql     | f
         |              |  SELECT 'Hello World' AS result;          +|          |         | 
         |              |                                            |          |         | 
 public  | testdataprod |                                           +| postgres | sql     | f
         |              |  INSERT INTO prod VALUES(1, 'みかん', 10);+|          |         | 
         |              |                                            |          |         | 
 public  | prod_name    |                                           +| postgres | sql     | f
         |              |  SELECT prod_name FROM prod AS result;    +|          |         | 
         |              |                                            |          |         | 
 public  | delete_prod  |                                           +| postgres | sql     | p
         |              | DELETE FROM prod WHERE price = 10;        +|          |         | 
         |              |                                            |          |         | 
(5 rows)
```

## 参照情報

* [PostgreSQL ユーザー定義関数](https://www.postgresql.jp/document/14/html/xfunc.html)
* [PostgreSQL CREATE FUNCTION](https://www.postgresql.jp/document/12/html/sql-createfunction.html)
* [PostgreSQL pg_proc](https://www.postgresql.jp/document/14/html/catalog-pg-proc.html)
* [PostgreSQL ユーザー定義プロシージャ](https://www.postgresql.jp/document/14/html/xproc.html)
* [PostgreSQL CREATE PROCEDURE](https://www.postgresql.jp/document/14/html/sql-createprocedure.html)
* [作成済みのファンクションを調べる](http://dbnote.web.fc2.com/note_func_info.html)
* [PostgreSQL CALL](https://www.postgresql.jp/document/14/html/sql-call.html)
* [PostgreSQL CREATE PROCEDURE](https://www.postgresql.jp/document/14/html/sql-dropprocedure.html)