FunctionとProcedureの違い
Function：返り値有り
Procedure：返り値無し
だけです．構文はLanguageに何を指定するかで変わります．
LanguageのSQLとPL/pgSQLの違い
SQL
通常のSQL構文です．制御構文はCASE程度しか使用できないため，制御上は簡便な処理しか記述できません．
languageとしてSQLを指定する利用用途としては，非常に長く煩雑なjoin操作を保存しておき，アプリケーション側でのクエリ組み立てを容易にすること等が想定されます．
重要な利点としては通常のSQLで記載されているので，他DBへ容易に移行できる互換性の高さが挙げられます．
PL/pgSQL
PostgreSQL独自の制御構文をSQLに加えて使用することができ，条件分岐・繰り返し等の制御構文を用いて，複雑な処理を記述可能です．
公式サイトでも利点として挙げられていますが，ClientとDBが同一デバイスに存在しない場合，PL/pgSQLを利用することでネットワーク負荷・応答性を向上することができます．
理由としては，結果を導出するにあたって，DBに対して複数回クエリを投げる必要がある場合，その回数分のネットワークラウンドトリップのレイテンシが発生しますが，Functionを用いた場合，一度の呼び出しで完結するためです．
欠点としては，PostgreSQL特有の構文であるため，他DBへの移植性が悪いということです．
とはいってもOracleのPL / SQLをならって記載されているため，多少の手直しで簡単に動作するようにはなります．
ちなみにPLはprocudure languageの略称で手続き型の処理を記述するための言語を指します．
Function – SQL
上記通り，複雑な処理をSQLで記載することはあまりないので，簡単に記載します
足し算をするFunction
様々な書き方が存在し，下記は全て同一のFunctionです．
create or replace function add(a integer, b integer) returns integer
as $$
select (a+b);
$$ language sql;
create or replace function add(a integer, b integer) returns integer
as 'select (a+b)' language sql;
create or replace function add(a integer, b integer) returns integer
language sql
return a + b;
create or replace function add(integer, integer) returns integer
as $$
select ($1+$2);
$$ language sql;
create or replace function add(integer, integer) returns integer
language sql
return ($1+$2);
create or replace function xx()
既に同一の関数が存在する場合は置き換え（オーバーロード），なければ通常通り作成します．
なお，PostgreSQLでは引数の型が違えば異なる関数として認識されます．
引数名
引数名は省略することが可能です．省略した場合は，定義の順番に応じて$1, $2…のようにアクセスことができます．
returns 返り値の型
返り値の型を指定します．
返り値
sql body内で返り値を決定します．あるいは最後尾にreturn句を使用することでそこに記載することも可能です．
単純な処理であればreturn句を用いた方がシンプルになります．ただし，このように様々な書き方が可能な場合にはプロジェクトでコーディング規約に明記し，統一した記法を採用した方がいいでしょう．
Function – PL/pgSQL
足し算をするFunction
PL / pgSQLのFunctionでは，bodyをbegin ~ end; で囲みます．
また，SQLで使用可能であった行末でのreturn句を使用することができず，必ずbody内部でreturnを指定します．
また，関数の引数にoutを指定することでも返り値を指定することができ，この場合はreturns句は指定できません．end;に達した点でその値に代入された値が返り値となります．
-- OK
create or replace function add(a integer, b integer) returns integer
as $$
begin
return (a+b);
end;
$$ language plpgsql;
-- OK
create or replace function add(integer, integer) returns integer
as $$
begin
return ($1+$2);
end;
$$ language plpgsql;
-- NG !!!!!!!
create or replace function add(integer, integer) returns integer
language plpgsql
return ($1+$2);
-- OK
create or replace function add(a integer, b integer, out sum integer)
as $$
begin
sum := a+b;
end;
$$ language plpgsql;
PL / pgSQLではこの他にも様々な機能を利用することが可能です．
変数宣言(declare)
単一値を受ける
例えばsomeTableに保存されているあるカラムcolumn1のmax値を利用した計算を行いたい場合は以下のように指定できます．
create or replace function add(a integer, b integer) returns integer
as $$
declare
c integer := (select max(column1) from someTable);
begin
return (a+b+c);
end;
$$ language plpgsql;
複数値を受ける（record / rowtype）
複数値を受けたい場合はrecordを指定します．なおrecordは厳密には形式は決まっておらず，任意の数のカラムを受けることが可能な単純なプレースホルダーです．受ける値がテーブル定義と同義であればrowtypeを指定した受けた方が安全です．
値の代入は結果がFunctionの返り値であれば初期化時に代入できますが、select文（サブクエリ）を発行する必要がある場合にはselect intoをbody内で使用します．
-- recordを用いた例
create or replace function add(a integer, b integer) returns integer
as $$
declare
row record;
row2 record := some_func() -- some_funcが複数値を返す関数の場合、初期化時に代入可能
c integer;
d integer;
begin
select * into row from someTable limit 1;
c = row.column1;
d = row.column2;
return (a+b+c+d);
end;
$$ language plpgsql;
受ける方が決まっている場合にはrowTypeを使用する．
-- rowtypeを用いた例
create or replace function add(a integer, b integer) returns integer
as $$
declare
row someTable%rowtype;
c integer;
d integer;
begin
select * into row from someTable limit 1;
c = row.column1;
d = row.column2;
return (a+b+c+d);
end;
$$ language plpgsql;
なお，サブクエリは複数カラムを返すことができませんので，宣言時にselectを用いて初期化することはできません．以下のような定義にすると
ERROR: subquery must return only one column
といったエラーが発生します．
-- NG例
create or replace function add10(a integer, b integer) returns integer
as $$
declare
row record := (select * from someTable limit 1); -- NG!!!!!!
c integer = row.column1;
d integer = row.column2;
begin
return (a+b+c+d);
end;
$$ language plpgsql;
条件（if – else）
if – elseは以下のような構文です．a, bの値を閾値で補正している例です．
良く忘れるのはend if; なので注意が必要です．
create or replace function add(a integer, b integer) returns integer
as $$
begin
if a < 0
then a = 0;
end if;
if b < 0
then b = 0;
elseif b > 100
then b = 100;
else
b = b;
end if;
return (a+b);
end;
$$ language plpgsql;
繰り返し（for）
標準的には以下のように使用します．掛け算を足し算で表現した例です．
create or replace function multiply(a integer, b integer) returns integer
as $$
declare
sum integer := 0;
begin
for i in 1..b loop
sum = sum + a;
end loop;
return sum;
end;
$$ language plpgsql;
一般的なContinue（次のイテレーションに進む）やExit（for分終了）を挟むこともできます．
create or replace function multiply(a integer, b integer) returns integer
as $$
declare
sum integer := 0;
begin
for i in 1..b loop
if i = 3 then continue; end if;
if i = 10 then exit; end if;
sum = sum + a;
end loop;
return sum;
end;
$$ language plpgsql;
代入(:= or =)
代入は実は:=でも=でもどちらでいいです．if文でbooleanが求められる場合には等値のオペレーターとして認識されます．誤解を招かないように:=で統一するコーディング規則としたほうがいい可能性はあります．
複数値（行）を返す
複数値を返す方法は複数あります。
単一行の場合は、return {expression};を使用します。
複数行が必要な場合setofを追加し、return next {expression} や return query {query}。
返り値のtableを定義する
複数値を返したい場合は以下のようにtableを定義します．新しくテーブルを定義した場合は複数行を返す場合でもsetofは必要ありません。
create or replace function makeRow(a integer, b integer)
returns table(
value1 integer,
value2 integer
)
as $$
begin
value1 = a;
value2 = b;
return next;
end;
$$ language plpgsql;
recordで返す
複数値を受ける章でも説明しましたが、recordは型が決まっていない任意のテーブル定義として利用することが可能です。 複数行返したい場合はsetofを追加します。
-- 1
create or replace function makeRow(a integer, b integer)
returns record
as $$
begin
return (a,b);
end;
$$ language plpgsql;
-- 2. out parameterを使用した場合
create or replace function makeRow(a integer, b integer, out value1 integer, out value2 integer)
returns record
as $$
begin
value1 = a;
value2 = b;
return;
end;
$$ language plpgsql;
1番目の場合、使用側ではテーブル定義を手動で導入する必要があります。
-- OK 定義あり select * from makeRow(1,2) as (s integer, ss integer); -- NG 定義なし select * from makeRow(1,2);
定義なしの場合、以下のようなエラーが発生します。
ERROR: a column definition list is required for functions returning “record”
既存のテーブル定義を利用する
また、既存のテーブル定義を利用することも可能です。複数行返却する場合にはsetofを追加する必要があります。
create or replace function makeRow(a integer, b integer)
returns someTable
as $$
begin
return (a, b);
end;
$$ language plpgsql;
return next / return query
ともに終端演算子ではありません。結果に対して実行した行を追加します。この文に差し掛かってもFunctionは継続されます。Functionが終了するのはreturn;、あるいは最後に辿り着いた時です。
return next
return nextは2通りの使用方法があります。
return next;は返り値としてテーブルを定義した場合にのみ使用可能です。それまでにアサインされた値をテーブル行として返します。アサインできていない列にはnullが入ります。
create or replace function makeRow(a integer, b integer)
returns table(
value1 integer,
value2 integer
)
as $$
begin
value1 = a;
return next;
value1 = b;
value2 = a;
return next;
end;
$$ language plpgsql;
select * from makeRow(1,2);
return nextは通常expressionを伴って使用されます。以下のような使用例が一般的です。
create or replace function makeRow(a integer, b integer)
returns setof someTable
as $$
declare r record;
begin
return next (b,a);
return next (a,b);
for r in (select * from someTable) loop
return next r;
end loop;
end;
$$ language plpgsql;
select * from makeRow(1,2);
someTableは単純に以下のような適当なテーブルです。
return query
クエリ結果をまとめて追加したい場合は、return queryを使用します。
create or replace function makeRow(a integer, b integer)
returns setof someTable
as $$
begin
return query select * from someTable;
end;
$$ language plpgsql;
ログを残す(raise)
Function / Procedure作成中にはコンソールでログを残したくなるはずです。raiseを使用することで実現できます。
create or replace function makeRow(a integer, b integer)
returns someTable
as $$
begin
raise info 'input parameter a=%,b=%', a, b;
return (a,b);
end;
$$ language plpgsql;
levelは以下から選べます。
| DEBUG | LOG | INFO | NOTICE | WARNING | EXCEPTION |
| デフォルト |
なお、デフォルトのEXCEPTIONのままだとこの時点でEXCEPTIONがThrowされて実行が中断されてしまいますので、適切なレベルを設定する必要があります。
なお、％はプレースホルダーで、記載順で置き換えられていきます。
select * from makeRow(1,2); -- console画面 input parameter a=1,b=2
