他のバージョンの文書9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

Chapter 23. PL/pgSQL - SQL 手続き言語

Table of Contents
23.1. 概要
23.1.1. PL/pgSQL を使用することの利点
23.1.2. PL/pgSQL の開発
23.2. PL/pgSQL の構造
23.3. 宣言
23.3.1. 関数引数の別名
23.3.2. 行型
23.3.3. レコード
23.3.4. 属性
23.3.5. RENAME
23.4.
23.5. 基本的な文
23.5.1. 代入
23.5.2. SELECT INTO
23.5.3. 式、もしくは、結果を持たない問い合わせの実行
23.5.4. 動的問い合わせの実行
23.5.5. 結果ステータスの取得
23.6. 制御構造
23.6.1. 関数からの復帰
23.6.2. 条件分岐
23.6.3. 単純なループ
23.6.4. 問い合わせ結果の繰返し
23.7. カーソル
23.7.1. カーソル変数の宣言
23.7.2. カーソルを開く
23.7.3. カーソルの使用
23.8. エラーとメッセージ
23.9. トリガプロシージャ
23.10.
23.11. Oracle PL/SQL からの移植
23.11.1. 主な違い
23.11.2. 関数の移植
23.11.3. プロシージャ
23.11.4. パッケージ
23.11.5. 注意すべきその他の事項
23.11.6. 付録

PL/pgSQL は、 PostgreSQLデータベースシステム用の読み込み可能な手続き言語です。

このパッケージは Jan Wieck 氏によって書かれたものを元にしています。このドキュメントの一部はRoberto Mello 氏()によって書かれました。

23.1. 概要

PL/pgSQL の設計目的は、次のような読み込み可能な手続き言語でした。

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

各式やSQL 問い合わせが初めてその関数で使用される時に、PL/pgSQL インタプリタは(SPI マネージャのSPI_prepareSPI_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 文を使った動的問い合わせを構成することで、この制限を回避できます。

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

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

23.1.1.1. よりよいパフォーマンス

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

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

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

23.1.1.2. SQL のサポート

PL/pgSQL は手続き言語の能力を SQL の柔軟性と平易性に加えたものです。 PL/pgSQL では、データ型、列、演算子、SQL 関数を全て使用することができます。

23.1.1.3. 移植性

PL/pgSQL 関数は PostgreSQL 内で実行されますので、これらの関数は PostgreSQL が稼働する全てのプラットフォームで実行することができます。このようにコードを再利用できますので、開発費用を低減できます。

23.1.2. PL/pgSQL の開発

特に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 を使う方法です。これを利用すると、単一引用符をエスケープさせたり、関数の作り直しやデバッグが容易に行えるという良い点があります。