ユーザー定義関数の作成
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 \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)