問い合わせ言語(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文のリストを実行し、そのリストの最後の問い合わせの結果を返します。
単純な（集合ではない）場合、最後の問い合わせの結果の最初の行が返されます。 最後の問い合わせが何も行を返さない時はNULL値が返されます。
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関数
引数が型のみの記述の場合、$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の実施
複合型をリテラル定数として記述するには、フィールド値をカンマで区切り、それらを括弧で括ります
testdb5=# select echo_prod('(4, "かき", 200)');
echo_prod
-----------------
(4," かき",200)
(1 row)
例2-4 引数が複合型の場合 その2
複合型の属性(要素)には、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 戻り値が集合の場合の定義
戻り値に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 戻り値が集合の場合の定義
戻り値に 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関数
戻り値を返さなくていいので、結果を返さない、通常のよく使われる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)