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

目次
37.1. 概要
37.1.1. PL/pgSQLを使用することの利点
37.1.2. 引数と結果データ型のサポート
37.2. PL/pgSQLによる開発向けのヒント
37.3. PL/pgSQLの構造
37.4. 宣言
37.4.1. 関数引数の別名
37.4.2. 型の複製
37.4.3. 行型
37.4.4. レコード型
37.4.5. RENAME
37.5.
37.6. 基本的な文
37.6.1. 代入
37.6.2. SELECT INTO
37.6.3. 結果を返さない式、もしくは問い合わせの実行
37.6.4. 動的コマンドの実行
37.6.5. 結果ステータスの取得
37.7. 制御構造
37.7.1. 関数からの復帰
37.7.2. 条件分岐
37.7.3. 単純なループ
37.7.4. 問い合わせ結果による繰返し
37.8. カーソル
37.8.1. カーソル変数の宣言
37.8.2. カーソルを開く
37.8.3. カーソルの使用
37.9. エラーとメッセージ
37.10. トリガプロシージャ
37.11. Oracle PL/SQLからの移植
37.11.1. 移植例
37.11.2. その他の注意事項
37.11.3. 付録

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

37.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サーバでサポートされているEXECUTE文とは関連がありません。 サーバのEXECUTE文は、PL/pgSQL関数内で使用することはできません(使用する必要もありません)。

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

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

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

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

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

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

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

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

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

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

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