PostgreSQLでは、一連の処理に名前をつけ、関数として定義することができる。これをユーザー定義関数という。
CREATE [OR REPLACE] FUNCTION 関数名(引数の型の並び) RETURNS 戻り値の型 AS $$
...
処理
...
$$ LANGUAGE 言語名;
言語名は処理を記述する文法の言語である。今回はSQLとPLPGSQLを使用する。
まずはSQL言語で関数を定義する。
次のＳＱＬをファイルとして作成。（plus091.sql）
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i plus091.sql
CREATE FUNCTION
関数を使用する。
関数の戻り値を単一の値（今回は整数）とみなせば、SELECTの直後で関数を呼び出す形式となる。oddtbs=> select plus091(10, 20); plus091 --------- 30 (1 row) oddtbs=> select * from plus091(10, 20); plus091 --------- 30 (1 row)
作成した関数を確認するSQLを準備する。
次のＳＱＬをファイルとして作成。（showfunc091.sql）
ログイン画面から実行。
oddtbs=> \i showfunc091.sql
proname | prolang
-----------+---------
plus091 | 14
...
SQL言語よりも、PL/PGSQL言語で処理を記述する方が高度なことができる。
その際、言語名はPLPGSQLとする。
PL/PGSQLは処理を、BEGIN ～ END;のブロックの中に記述する。また、戻り値はRETURNで返す。
CREATE [OR REPLACE] FUNCTION 関数名(引数の型の並び) RETURNS 戻り値の型 AS $$
BEGIN
...
処理
...
RETURN 戻り値;
END;
$$ LANGUAGE 言語名;
次のＳＱＬをファイルとして作成。（plus092.sql）
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i plus092.sql
CREATE FUNCTION
関数を使用する。
oddtbs=> select plus092(10, 20); plus092 --------- 30 (1 row) oddtbs=> select * from plus092(10, 20); plus092 --------- 30 (1 row)
関数の確認
oddtbs=> \i showfunc091.sql
proname | prolang
-----------+---------
plus091 | 14
plus092 | 16386
...
前回定義したものと言語が異なっていることが確認できる。
複数行返す関数は、戻り値として、SETOF ～を指定する。
戻り値は、RETURN NEXT ～で複数行をため込み、RETURNでそれを返す。
CREATE [OR REPLACE] FUNCTION 関数名(引数の型の並び) RETURNS SETOF ～ AS $$
BEGIN
...
RETURN NEXT ～;
RETURN NEXT ～;
...
RETURN;
END;
$$ LANGUAGE 言語名;
次のＳＱＬをファイルとして作成。（setof091.sql）
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i setof091.sql
CREATE FUNCTION
関数を使用する。
oddtbs=> select setof091(); ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "setof091" line 2 at RETURN NEXT ※ 今回は戻り値が複数行あり、単一のデータではないため、SELECTの直後で関数を呼び出す形式はエラーとなった。 oddtbs=> select * from setof091(); setof091 ---------- 10 20 30 (3 rows)
・変数宣言はDECLAREブロックで行う必要がある。
・変数宣言は、変数名 型となる。
・代入は、:=を使用する。
CREATE [OR REPLACE] FUNCTION 関数名(引数の型の並び) RETURNS 戻り値の型 AS $$
DECLARE
変数名 型 [:= 値];
…
BEGIN
...
変数名 := 値;
...
END;
$$ LANGUAGE 言語名;
次のＳＱＬをファイルとして作成。（var091.sql）
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i var091.sql
CREATE FUNCTION
関数を使用する。
oddtbs=> select * from var091();
var091
--------
10
20
30
(3 rows)
・関数定義の際の$$ ～ $$は、実は単なる文字列を意味している。これをダラーコーテーションという。
・ダラーコーテーションは$と$の間に、任意の文字列を含むことができる。$aa$ ～ $aa$など
・ダラーコーテーションの内側では、文字列を通常通り' ～ 'で表すことができる。
次のＳＱＬをファイルとして作成。（var092.sql）
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i var092.sql
CREATE FUNCTION
関数を使用する。
oddtbs=> select * from var092();
var092
------------------
i1の値は10です。
i2の値は20です。
i3の値は30です。
(3 rows)
一般的な条件分岐の文法。
IF 条件式 THEN
...
ELSIF 条件式 THEN
...
ELSE
...
END IF;
ELSIFの綴りに注意。ただし、最近のバージョンではELSEIFでもＯＫ。
次のＳＱＬをファイルとして作成。（ifelse091.sql）
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i ifelse091.sql
CREATE FUNCTION
関数を使用する。
oddtbs=> \t Showing only tuples. oddtbs=> select ifelse091(13); １０以上です。 oddtbs=> select ifelse091(7); ５以上です。 oddtbs=> select ifelse091(3); ５未満です。 oddtbs=> \t Tuples only is off.
繰り返し構文。基本的に無限ループなので、EXIT WHEN ～などで抜ける処理が必要。
LOOP
...
EXIT WHEN 条件式;
...
END LOOP;
次のＳＱＬをファイルとして作成。（loop091.sql）
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i loop091.sql
CREATE FUNCTION
関数を使用する。
oddtbs=> select * from loop091();
loop091
---------
1回目
2回目
3回目
4回目
5回目
(5 rows)
繰り返し構文。繰り返す条件をブロックの先頭で記述する。
WHILE 条件式 LOOP
...
...
END LOOP;
次のＳＱＬをファイルとして作成。（while091.sql）
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i while091.sql
CREATE FUNCTION
関数を使用する。
oddtbs=> select * from while091();
while091
----------
1回目
2回目
3回目
4回目
5回目
(5 rows)
繰り返し構文。カウンタを自動的に操作する。
FOR カウンタ変数名 IN 初期値..終了値 LOOP
...
...
END LOOP;
次のＳＱＬをファイルとして作成。（for091.sql）
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i for091.sql
CREATE FUNCTION
関数を使用する。
oddtbs=> select * from for091();
for091
--------
1回目
2回目
3回目
4回目
5回目
(5 rows)
複数列を返す関数を定義するために、列の並びを型（TYPE）として定義し、関数の戻り値でその型を指定する。
type01という型を作成
CREATE TYPE 型名 AS (列名 型, 列名 型, ...);
oddtbs=> create type type01 as (num integer, data text);
CREATE TYPE
次のＳＱＬをファイルとして作成。（multi091.sql）
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i multi091.sql
CREATE FUNCTION
関数を使用する。
oddtbs=> select * from multi091();
num | data
------+--------
1001 | DATA_1
(1 row)
関数の戻り値で、定義した型をSETOFの後指定する。
次のＳＱＬをファイルとして作成。（multi092.sql）
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i multi092.sql
CREATE FUNCTION
関数を使用する。
oddtbs=> select * from multi092();
num | data
------+--------
1001 | DATA_1
1002 | DATA_2
1003 | DATA_3
1004 | DATA_4
1005 | DATA_5
(5 rows)
・RAISEという演算子によって、メッセージの出力ができる。
・RAISEはメッセージのレベル（緊急度が低い順に、DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION）を指定する。
・レベルにEXCEPTIONを指定すると、処理が異常終了する。
・メッセージ文字列の%の部分に、変数の値が埋め込まれる。
RAISE メッセージの種類 '...%...%...', 変数, 変数...;
次のＳＱＬをファイルとして作成。（raise091.sql）
voidは戻り値がないことを意味する。
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i raise091.sql
CREATE FUNCTION
関数を使用する。
oddtbs=> select raise091();
NOTICE: 1回目
NOTICE: 2回目
NOTICE: 3回目
NOTICE: 4回目
ERROR: 5回目
5回目で異常終了しているので6回目以降は実行されていない。
FORループを使用して、INSERTを行う。
次のＳＱＬをファイルとして作成。（insert091.sql）
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i insert091.sql
...
関数を使用する。
oddtbs=> select insert091();
insert091
-----------
(1 row)
確認する。
oddtbs=> select * from t1;
num | data
-----+--------
1 | DATA_1
2 | DATA_2
3 | DATA_3
4 | DATA_4
5 | DATA_5
6 | DATA_6
7 | DATA_7
8 | DATA_8
9 | DATA_9
(9 rows)
二重FORループを使用して、INSERTを行う。
次のＳＱＬをファイルとして作成。（insert092.sql）
上記のSQLを実行し、関数自体を定義する。
oddtbs=> \i insert092.sql
...
関数を使用する。
oddtbs=> select insert092();
insert092
-----------
(1 row)
確認する。
oddtbs=> select * from t2;
num1 | num2 | data
------+------+----------
1 | 1 | DATA_1_1
1 | 2 | DATA_1_2
1 | 3 | DATA_1_3
9 | 7 | DATA_9_7
9 | 8 | DATA_9_8
9 | 9 | DATA_9_9
(81 rows)