問い合わせ言語(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)

参照情報