CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$; は、組み込み関数と同じようにクエリから呼び出せる関数を定義します。本体は $$ の間に、普通の SQL か、PostgreSQL の手続き型言語である PL/pgSQL で書きます。代わりに CALL で実行するルーチンを定義するのが CREATE PROCEDURE (PostgreSQL 11 以降) です。
with_tax(50) はデフォルトの税率を使って 60 を返し、with_tax(50, 0.1) は 55 を返します。引数は名前でも渡せます: with_tax(50, rate => 0.1)。
PL/pgSQL でロジックを書く
関数に変数、IF、ループ、エラー処理が必要なら LANGUAGE plpgsql を使います。変数は DECLARE セクションに、コードは BEGIN と END の間に書きます。
4 kg の荷物は基本料金の 5、20 kg の速達は (5 + 10 * 0.5) * 2 = 20 です。PL/pgSQL の中では、RAISE NOTICE 'value: %', cost; でメッセージを表示し、RAISE EXCEPTION でエラーにして中断します。
普通の SQL で書けるロジックなら LANGUAGE sql を選びましょう。単純な SQL 関数は、プランナーが呼び出し元のクエリにインライン展開できます。
行の集合を返す
RETURNS TABLE (...) を使うと、行を返す関数になります。FROM の中でテーブルのように呼び出します。
orders_of('ana') は注文 1 と 3 を返します。RETURNS SETOF orders なら、指定した列ではなく orders テーブルの行全体を返します。
PostgreSQL 14 以降、SQL 関数の本体は文字列ではなく標準 SQL で書けます。PostgreSQL は本体を解析済みの形で保存し、使っているテーブルと列を記録するので、関数がある間はそれらを削除できません。
CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
SELECT count(*) FROM orders WHERE customer = who;
END;
ストアドプロシージャを作り CALL で実行する
プロシージャには戻り値の型がなく、CALL で実行します。データを変更でき、CALL が明示的なトランザクションブロックの中になければ、途中で COMMIT できます。大きな処理をバッチに分けて進めるときはこの方法を使います。
口座 1 は 70、口座 2 は 80 になります。200 を送金すると口座 1 の CHECK 制約に違反し、そのエラーで両方の更新がロールバックされます。
| 関数 | プロシージャ | |
|---|---|---|
| 実行方法 | SELECT f(...)、クエリのどこでも | CALL p(...) のみ |
| 戻り値 | 値、1行、または行の集合 | なし (または OUT/INOUT パラメーター) |
内部での COMMIT / ROLLBACK | 不可 | PL/pgSQL で、トランザクションブロック内から呼ばれていなければ可能 |
| 導入時期 | 当初から | PostgreSQL 11 |
揮発性を指定する: IMMUTABLE、STABLE、VOLATILE
揮発性のラベルは、関数について何を前提にしてよいかをプランナーに伝えます。デフォルトは VOLATILE です。
IMMUTABLE: 結果は引数だけで決まり、決して変わらない (with_tax)。インデックスの式で使う関数にはこれが必要です。STABLE: 結果は文と文の間では変わりうるが、1つの文の中では変わらない。たとえばテーブルを読む関数 (orders_of) です。VOLATILE: それ以外のすべて。random()を使う関数やデータを変更する関数などです。PostgreSQL は行ごとに呼び出し直します。
ラベルを間違えると結果も間違います。テーブルを読む IMMUTABLE 関数は、インデックスやキャッシュされた実行計画から古い値を返すことがあります。
関数の置き換え、オーバーロード、削除
CREATE OR REPLACE FUNCTION は既存の関数の本体を変更しますが、戻り値の型は変えられません。変えるには先に削除します。PostgreSQL は関数を名前と引数の型で識別するので、f(int) と f(text) は別の関数です。同じ名前の2つの関数のうち1つを削除するには、引数の型を指定します。
CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;
ループ、カーソル、例外、EXECUTE による動的 SQL は PL/pgSQL の章 に記載されています。行が変わったときに関数を自動で実行するには、トリガー に関数を割り当てます。
よくある質問
PostgreSQL の関数とプロシージャの違いは何ですか?
関数は値を返し、クエリの中で呼び出します (SELECT f(x))。PostgreSQL 11 で追加されたプロシージャは CALL で単独で実行し、本体の中で COMMIT や ROLLBACK ができます。関数は常に呼び出し側のトランザクションの中で動きます。
PostgreSQL の関数にある $$ は何を意味しますか?
$$ はドル引用符です。文字列リテラルの始まりと終わりを示すので、関数の本体に単一引用符をエスケープせずに書けます。$body$ ... $body$ のようにタグを付けることもできます。
PostgreSQL の関数のコードを見るには?
psql では \df で関数を一覧し、\sf name で関数の定義を表示します。SQL では SELECT pg_get_functiondef('name'::regproc); が完全な CREATE OR REPLACE FUNCTION 文を返します。
PostgreSQL の関数は複数の行を返せますか?
返せます。RETURNS TABLE (col type, ...) か RETURNS SETOF type で宣言し、FROM 句でテーブルのように呼び出します: SELECT * FROM f(1);。