第 36章PL/pgSQL - SQL手続き言語

目次
36.1. 概要
36.1.1. PL/pgSQLを使用することの利点
36.1.2. 引数と結果データ型のサポート
36.2. PL/pgSQLによる開発向けのヒント
36.3. PL/pgSQLの構造
36.4. 宣言
36.4.1. 関数引数の別名
36.4.2. 型の複製
36.4.3. 行型
36.4.4. レコード型
36.4.5. RENAME
36.5.
36.6. 基本的な文
36.6.1. 代入
36.6.2. SELECT INTO
36.6.3. 結果を返さない式、もしくは問い合わせの実行
36.6.4. まったく何もしない
36.6.5. 動的コマンドの実行
36.6.6. 結果ステータスの取得
36.7. 制御構造
36.7.1. 関数からの復帰
36.7.2. 条件分岐
36.7.3. 単純なループ
36.7.4. 問い合わせ結果による繰り返し
36.7.5. エラーの捕捉
36.8. カーソル
36.8.1. カーソル変数の宣言
36.8.2. カーソルを開く
36.8.3. カーソルの使用
36.9. エラーとメッセージ
36.10. トリガプロシージャ
36.11. Oracle PL/SQLからの移植
36.11.1. 移植例
36.11.2. その他の注意事項
36.11.3. 付録

PL/pgSQLは、PostgreSQLデータベースシステム用の読み込み可能な手続き言語です。 PL/pgSQLの設計目的は、次のような読み込み可能な手続き言語でした。

ユーザ定義型用の入出力変換と計算関数を除き、C言語関数で定義できる事は全てPL/pgSQLでも実現できます。 例えば、複雑な条件のある演算処理関数の作成が可能ですし、作成した関数を使用して演算子を定義することも、インデックス式にその関数を使用することも可能です。

36.1. 概要

PL/pgSQL呼び出しハンドラは関数のソーステキストを解析し、初めてその関数が(各セッションで)呼び出された時にバイナリ形式の命令ツリーを内部で作成します。 この命令ツリーは完全にPL/pgSQL文構造に変換されますが、関数内部の個々のSQL式とSQLコマンドは即座に変換されません。

各式やSQLコマンドが初めてその関数で使用される時に、PL/pgSQLインタプリタは(SPIマネージャのSPI_prepareSPI_saveplan関数を使用して)実行計画の準備を行います。 その後にその式やコマンドが行われる時には、その準備された計画を再利用します。 こうして、実行計画が必要とされる問い合わせを内包した条件付きコードを持つ関数では、そのデータベース接続が有効な間実際に使用された部分についてのみ、計画の準備と保存が行われます。 これにより、解析にかかる総時間をかなり短縮し、PL/pgSQL関数の文の問い合わせ計画を生成することができます。 欠点は特定の式や問い合わせのエラーが、関数の該当部分が実行されるまで検出されないことです。

PL/pgSQLが関数内の特定のコマンド用の問い合わせ計画を作成すると、そのデータベース接続が有効な間、その計画は再利用されます。 通常これにより性能は向上しますが、動的にデータベーススキーマを変更する場合は問題がいくつか発生します。 例えば、以下のようにします。

CREATE FUNCTION populate() RETURNS integer AS $$
DECLARE
    -- 宣言部
BEGIN
    PERFORM my_function();
END;
$$ LANGUAGE plpgsql;

上の関数を実行すると、PERFORM文用に生成された問い合わせ計画では、my_function()のOIDを参照します。 後に、my_function()を削除し、再作成すると、populate()my_function()を見つけることができなくなります。 その場合、新たにコンパイルされるようにpopulate()を再作成、または、少なくともデータベースセッションを新しく起動しなければなりません。 この他に、my_function()の定義を更新する時に、CREATE OR REPLACE FUNCTIONを使用することでこの問題を防ぐことができます (関数が"置き換えられる"時に、そのOIDが変わりません)。

このようにPL/pgSQLは実行計画を保存しますので、PL/pgSQL関数内に直接現れるSQLコマンドは実行の度に同じテーブルとフィールドを参照しなければなりません。 つまり、SQLコマンドにて、テーブルやフィールドの名前としてパラメータを使用することができません。 実行の度に新しく問い合わせ計画を作成する無駄を覚悟で、PL/pgSQLEXECUTE文を使った動的問い合わせを構成することで、この制限を回避できます。

注意: PL/pgSQL EXECUTE文はPostgreSQLサーバでサポートされているEXECUTESQL文とは関連がありません。 サーバのEXECUTE文はPL/pgSQL関数内で使用することはできません(使用する必要もありません)。

36.1.1. PL/pgSQLを使用することの利点

SQLPostgreSQLおよびその他のほとんどのリレーショナルデータベースが問い合わせ言語として使用している言語です。 移植性があり、習得が容易です。 しかし、あらゆるSQL文はデータベースサーバによって個々に実行されなければいけません。

これはクライアントアプリケーションに対して以下のようなことを要求しています。 まず、データベースサーバに問い合わせを送信します。 次にそれが処理されるのを待ちます。 次に、結果を取得します。 次に若干の計算を行います。 そして、サーバに次の問い合わせを送信します。 クライアントがデータベースサーバマシンと異なるマシンの場合、プロセス間通信を招き、ネットワーク・オーバーヘッドを起こすかもしれません。

PL/pgSQLを使うことで、計算と複数の問い合わせをデータベースサーバ内部でひとまとめに実行することができます。 このように、手続き言語の能力とSQLの使いやすさを持ち合わせているにもかかわらず、全てにおいてクライアント/サーバ通信のオーバーヘッドがないのでかなりの処理時間を節約できます。 これによりかなり性能を向上させることができます。

また、PL/pgSQLではSQL全てのデータ型、演算子、関数を使用することができます。

36.1.2. 引数と結果データ型のサポート

PL/pgSQLで作成された関数は、サーバでサポートされる任意のスカラデータ型や配列データ型を引数として受け付けることができ、また、これらの型を結果として返すことができます。 また、任意の、名前で指定された複合型(行型)を受け付けたり、返したりすることもできます。 さらに、PL/pgSQL関数を、項7.2.1.4で説明される、呼び出す問い合わせ中の仕様で決定される列を持つ行型である、recordを返すように宣言することも可能です。

また、PL/pgSQL関数を、anyelementanyarray多相型を受け付けたり、返したりするように宣言することもできます。 項32.2.5の説明の通り、多相型関数で扱われるデータ型は呼び出しごとに変動することができます。 例を項36.4.1に示します。

PL/pgSQL関数を、"集合"、テーブル、1つのインスタンスを返す任意のデータ型を返すように宣言することもできます。 こうした関数は、結果集合の必要な要素に対してRETURN NEXTを実行することで、その出力を生成します。

最後に、有用な戻り値を持たない場合、PL/pgSQL関数は、voidを返すように宣言することができます。

注意: PL/pgSQL は現在ドメイン型を完全にはサポートしていません。 ドメインは基本的なスカラ型と同様に取り扱われます。 これはドメインに関する制約が、実施されないことを意味します。 これは関数の引数に関する問題ではありませんが、ドメイン型を戻り値とするPL/pgSQL関数を宣言する時の障害となります。

PL/pgSQL関数は戻り値の型を明確に指定する代わりに、出力パラメータと共に宣言することもできます。 これは言語に対して基本的な能力を追加するものではありませんが、特に複数の値を返すときにしばしば便利です。

特殊な例が項36.4.1及び項36.7.1に見られます。