postgreSQL その31 トリガ、ルール、スキーマのつづき
postgreSQL その8 課題リストは2018/4/30時点でのオイラの所見
[P.253]
■関数
【概要】
PostgreSQLでは、データベースに関数を定義することができる。
一般的にはストアドプロシージャと呼ばれている。 ストアドプロシージャでは、DBMSに備わっている便利な関数や機能を利用できるので、アプリケーションが出の複雑な処理をDBMS側に任せることができる。 また、大量のデータをアプリケーション側へ渡されずにデータベースで処理できるので、ネットワークの負荷軽減などにも有用。
【おいらの考え】
RDBMS側の関数（ストアドプロシージャ）を使うと、もしデータを他のDBMSに移行する際の可搬性が損なわれる。パフォーマンスに影響が小さいなら、アプリケーション側の機能を使う方が可搬性は高くなる。
【言語の登録】
PostgreSQLでは、関数の記述にいくつかの言語を使用することができる。
デフォルトでは、SQLと、PostgreSQL特有の手続き型言語であるPL/pgSQLが使用できる。
PL/pgSQLでは、SQLで記述できないループや条件分岐などの複雑な処理を定義することができる。
その他、PL/PerlやPL/Pythonといった言語もサポートしている（それらの言語を使用する場合はcreatelangで登録する）
【関数の定義】
[書式]
create [or replace] function 関数名(引数) returns [setof] 戻り値 as $$
関数の記述
$$ language 使用言語;
or replaceを付与すると、既存の同名関数を上書きする。「戻り値」には、integerやtextなどのデータ型を指定できるほか、特定のテーブル名を指定することで、そのテーブルの行と同じデータを戻り値とすることができる。
複数の戻り値を取る場合には、setofを付与する。
「使用言語」には、関数を記述する言語を指定する。
「引数」の表現方法はいくつかある。代表的なものは、引数となるデータ型を記述するか、変数名とデータ型のセットを記述する。前者は、引数を関数内部において$nで利用し、後者は、引数を関数内部において変数名で利用する。
次の例は、簡単なSQLで記述した関数。
[例]
testdb=> select * from tb1;
col1 | col2
------+------
1 | 1
2 | 2
3 | 3
(3 rows)
testdb=> create function add_func(int,int) returns bigint as $$
select ($1 + $2)::bigint /* 引数2つを足す*/
$$ language sql;
CREATE FUNCTION
testdb=> create function insert_func(int, text) returns setof tb1 as $$
select * from tb1 where col1 = $1 and col2 = $2
$$ language sql;
CREATE FUNCTION
【関数の構造】
ここでは、PL/pgSQLで記述する関数の構造を解説する。関数の構造は基本的に次の形となる。
[書式]
declare
変数の宣言
begin
関数の処理記述
end;
DECLAREの部分には、関数内部で使用する変数の宣言を記述する。
変数の宣言は次のような書式となる。
[書式]
変数名 [constant] データ型 [not null] [ {deault | := } 値など ];
DEFAULTによりデフォルト値を指定しない場合、変数の初期値はNULLとなる。
CONSTANTを付与すると、その変数への値の代入は禁止される。
変数へ値を代入する際には「 := 」という書式を使う。
次にしますのは、変数宣言の例。
[例]
・integer型でデフォルト値を10賭した変数var_1の宣言
var_1 int default 10;
・text型でSampleを固定値とした変数var_2の宣言
var_2 constant text := 'Sample';
関数の処理記述では、変数を利用したSQLやループ処理、条件分岐などを定義できる。
コメントは「 - コメント 」あるいは、「/* コメント */ 」の形式を受け付ける。
ループ処理では、loop、exit、continue、while、ｆorといった文を、
条件分岐では、if、case文をそれぞれ使用できる。
ループ処理や条件分岐は、ネストにすることも可能。
次に、簡単な関数の記述例を示す。
[例]
testdb=> create or replace function test_func(int, timestamp) returns integer as
testdb-> $$
testdb$> declare
testdb$> r timestamp; -- LOOPで使用する変数の宣言
testdb$> result int := 0; -- 戻り値に使用する変数の宣言
testdb$> begin
testdb$> for r in select c2 from tb1 where c1 = $1 -- FOR文でc2の値を逐次r屁代入
testdb$> loop
testdb$> if r < $2 -- 第二引数のtimestampの情報より古い日付かチェック
testdb$> then
testdb$> result := result + 1; -- IFの結果が真ならresultをインクリメント
testdb$> end if; -- IFの終了
testdb$> end loop; -- LOOPの終了
testdb$> return result; -- 戻り値を返す
testdb$> end
testdb$> $$
testdb-> language plpgsql;
CREATE FUNCTION