PL/pgSQL は、PostgreSQLデータベースシステム用の読み込み可能な手続き言語です。
このパッケージのオリジナルは、Jan Wieck氏によって書かれました。このドキュメントの一部はRoberto Mello 氏(<rmello@fslc.usu.edu>)によって書かれました。
PL/pgSQL の設計目的は、次のような読み込み可能な手続き言語でした。
関数とトリガプロシージャを作成するために使用できること
SQL言語に制御構造を追加すること
複雑な演算が可能であること
すべてのユーザ定義型、関数、演算子を継承すること
サーバによって信頼できるものと定義できること
使いやすいこと
PL/pgSQL 呼び出しハンドラは関数のソーステキストを解析し、初めてその関数が(何らかのバックエンドプロセスの中で)呼び出された時にバイナリ形式の命令ツリーを内部で作成します。この命令ツリーは完全に PL/pgSQL 文構造に変換されますが、関数内部の個々の SQL 式と SQL 問い合わせは即座に変換されません。
各式やSQL 問い合わせが初めてその関数で使用される時に、PL/pgSQL インタプリタは(SPI マネージャのSPI_prepare、SPI_saveplan 関数を使用して)実行計画の準備を行います。その式や問い合わせが次に行われる時には、その準備された計画を再利用します。 こうして、実行計画が必要とされる問い合わせを多く持つ、条件付きコードを持つ関数では、そのデータベース接続が有効な間実際に使用された部分についてのみ、計画の準備と保存が行われます。 これにより、解析にかかる総時間をかなり短縮し、手続き言語関数の文の問い合わせ計画を生成することができます。欠点は特定の式や問い合わせのエラーが、関数の該当部分が実行されるまで検出されないことです。
関数内の特定の問い合わせ用の 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()を再作成、または、少なくともデータベースセッションを新しく起動しなければなりません。
このように PL/pgSQL は実行計画を保存しますので、PL/pgSQL 関数内に直接現れる問い合わせは実行の度に同じテーブルとフィールドを参照しなければなりません。 つまり、問い合わせにて、テーブルやフィールドの名前としてパラメータを使用することができません。実行の度に新しく問い合わせ計画を作成する無駄を覚悟で PL/pgSQLの EXECUTE 文を使った動的問い合わせを構成することで、この制限を回避できます。
Note: PL/pgSQL EXECUTE 文は、PostgreSQL のバックエンドでサポートされている EXECUTE 文とは関連がありません。 バックエンドの EXECUTE 文は、PL/pgSQL 関数内で使用することはできません (使用する必要もありません)。
ユーザ定義型用の入出力変換と計算関数を除き、C言語関数で定義できる事はすべて PL/pgSQL でも実現できます。複雑な条件のある演算処理関数の作成、作成した関数を使った演算子の定義、関数インデックスに作成した関数を使用することが可能です。
よりよいパフォーマンス (Section 19.1.1.1参照)
SQL サポート (Section 19.1.1.2参照)
可搬性 (Section 19.1.1.3参照)
SQL は、PostgreSQL (およびその他のほとんどのリレーショナルデータベース) が問い合わせ言語として使用している言語です。可搬性があり、習得が容易です。しかし、あらゆるSQL 文は、データベースサーバによって個々に実行されなければいけません。
これはクライアントアプリケーションに対して以下のようなことを要求しています。 まず、データベースサーバに問い合わせを送信します。 次にそれが処理されるのを待ちます。 次に、結果を取得します。 次に若干の計算を行います。 そして、サーバに次の問い合わせを送信します。クライアントがデータベースサーバマシンからみて異なるマシンの場合、内部プロセス通信を招き、ネットワーク・オーバーへッドを起こすかもしれません。
PL/pgSQL を使うことで、計算と複数の問い合わせをデータベースサーバ 内部にひとまとめに実行することができます。 このように、手続き言語の威力と SQL の使いやすさを持ち合わせているにもかかわらず、すべてにおいてクライアント/サーバ通信のオーバーへッドがないのでだいぶ時間を節約できます。これによりかなり性能を向上させることができます。
特にOracle の PL/SQL のような他のデータベースの手続き言語で開発したことがあるならば、PL/pgSQL における開発はとても入りやすいでしょう。PL/pgSQL で開発する2つの良い方法を紹介します。
テキストエディタとpsqlを使ってファイルを再読み込みする方法を使用すること
PostgreSQLの GUI ツールである PgAccess を使うこと
PL/pgSQL で開発する一つの良い方法は単純に、関数を作成するのに自分の好きなテキストエディタを使い、もう一つのウィンドウで、psql (PostgreSQL 対話式モニタ) を使用して関数を読み込ませることです。この方法で行う場合には CREATE OR REPLACE FUNCTION を使用して関数を作成する方がよいでしょう。この方法によりファイルを再読み込みすることで、関数定義を更新することができます。たとえば、以下のようにします。
CREATE OR REPLACE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS ' .... end; ' LANGUAGE 'plpgsql';
psql を実行し、以下のように関数定義ファイルを読み込み、または、再読み込みすることができます。
\i filename.sql
その後すぐに、関数を試験するために SQL コマンドを発行することができます。
PL/pgSQL における開発のもう一つの良い方法は、PostgreSQLの GUI ツールである PgAccess を使う方法です。これを利用すると、単一引用符をエスケープさせたり、関数の作り直しやデバッグが容易に行えるという良い点があります。