ストアドプロシージャ、ストアドファンクションは、DBに登録(stored)する関数で、PostgreSQLの標準機能を利用する場合、PL/pgSQLで書きます。
ストアドプロシージャの使いどころ
ストアドプロシージャじたいはある程度SQL標準で規格化されており、多くのメジャーなDBMSがSQLにIF制御や変数などを拡張した機能を持ちます。
基本形は上から順に複数の文を実行する手続き型の設計で、SQLを用いて関数を書きます。
ファンクションとプロシージャの違いは主に戻り値の有無です。
呼び出し方は、ファンクションは組込み関数と同様、プロシージャの場合には
CALLで実行します。
-- ストアドファンクションの実行例
somedb=# SELECT * FROM some_function('some_arg');
-- ストアドプロシージャの実行例
somedb=# CALL some_procedure('some_arg');
これらの有効な使いどころは、クエリの削減です。
普及したアプリケーションフレームワークはORマッパーを備えており、アプリケーション側でデータを加工する設計になります。
必要なテーブルをアプリケーションに転送しなくてはならず、クエリ数も増えます。ORマッパーの典型的なボトルネックは「n+1クエリ」として知られています。
複雑なテーブル操作をストアド関数やストアドプロシージャとして実装すると、中間段階のクエリとテーブル転送がなくなります。
テーブル上で連続操作すべき処理については、ストアドプロシージャを使わないと不効率を避けられないものもあります。
ストアドプロシージャの構文
プロシージャはCREATE [OR REPLACE] PROCEDURE文で登録できます。
BEGINとENDの間に手続きを記述します。また、この例では宣言するだけになっていますが、DECLAREブロックに変数宣言できます。
CREATE OR REPLACE PROCEDURE some_procedure(a_some_arg text) AS $$
DECLARE
v_some_var bigint;
BEGIN
INSERT INTO some_table (name) VALUES (a_some_arg);
END;
$$ LANGUAGE plpgsql;
ストアドファンクションの構文
ストアドファンクションはプロシージャとほぼ同様ですが、RETURNで値を返す点が異なります。
CREATE OR REPLACE FUNCTION some_procedure(a_some_arg text) RETURNS bigint AS $$
DECLARE
v_some_var bigint;
BEGIN
SELECT id INTO v_some_var FROM some_table WHERE name = a_some_arg LIMIT 1;
RETURN v_some_var;
END;
$$ LANGUAGE plpgsql;
値の返し方
ストアド関数では値を返す必要があり、シグネチャのRETURNS句で型を指定します。そして、処理ブロックに結果を返す文が必要です。
SELECT INTOを使うとクエリ結果を変数に格納できます。
最終的にRETURN文に変数を指定することで、値を返せます。RETURNにはバリエーションがあり、途中で関数を終了しないRETURNがあるため、
リファレンスをよく理解する必要があります。
これ以外の書き方もありますが、IFなどで分岐することを考えると、変数への格納は便利な一手です。
ストアドプロシージャ実装のポイント
- プロシージャ・関数内から別のプロシージャ・関数を呼び出せる。長い手続きの構造化に有効
- 型は厳密にチェックされるため、テーブル定義などとの一致確認が重要。psqlの場合、
\dコマンドでカラム型を確認できる - 変数名の重複に弱いため、引数や変数には
v_などプレフィクスをつけて区別した方が安全。名前の区別がつかない場合、実行時エラーになる- 変数名を変えられない場合、特別コマンド #variable_conflict use_columnを利用する
CREATE時にsyntax errorは検出できるが、DBオブジェクトの指定ミスやambiguousなオブジェクト(複数テーブルにあるidなど)のエラーは実行しないと検出不能SELECTを使う場合、INTO句で値を利用しないとエラーになる。その場合、SELECTを``PERFORMに置き換える。IF NOT FOUNDなどの副作用はPERFORM`でも有効に動作する- 引数を変更する場合には、
OR REPLACE句で差し替えできないため、DROP PROCEDURE|FUNCTIONで削除のうえ再作成する必要がある - 引数を増やす場合などアリティが変わる場合、同名の別プロシージャが作成されるため注意が必要。オブジェクト指向のオーバーロードのような挙動になり分かりづらい
- バルク処理のリクエストも可能。
CREATE TYPEで複合型を作成しておくと、引数にクラスオブジェクトのような構造を渡せる。some_type[]のように複合型の配列も利用可能
デプロイ
上述のテンプレートの通り、ストアド機能のデプロイとはCREATE [OR REPLACE] PROCEDURE|FUNCTION文を実行することです。
一例として、
ridgepoleを利用すると、テーブルスキーマと同時にデプロイできます。
ridgepoleはexecute()文でSQLを直接実行でき、ストアド機能もCREATE OR REPLACEで実行すると べき等にデプロイできます。
また、以下のように別ファイルに切り出すことも可能です。純粋なSQL部分のファイルに分割すると、コードエディタのハイライトなどの支援が適切に機能します。
execute File.read("#{__dir__}/stored-proc.sql")
DBMSはデプロイ手段の貧弱さがネックになりやすく、プロシージャも含めて"DB Schema as code"を完備できれば使いやすくなります。
まとめ
ストアドプロシージャは古典的な技術ですが、モデルの複雑化に対して最終解に近いソリューションを提供しています。
複雑なモデルほど威力を発揮する機能のため、PL/pgSQLを書く際には難解なコードを書くことになります。
しかしそれは多次元モデルの集合操作の複雑さから直接来ているものであって、SQLはソリッドに扱える構文を提供できています。
厳密に取り扱うべきデータ処理は簡潔に記述できないため、SQLのようにムダなく記述できることをもって納得すべきでしょう。
Chuma Takahiro
関連記事
