PL/pgSQL

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

このパッケージは Jan Wieck 氏によって記述された物を元にしています。

概要

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

PL/pgSQL 呼び出しハンドラは関数のソーステキストを分析し、初めて バックエンドでその関数が呼び出された時にバイナリ形式の命令ツリー を内部で作成します。作成されたバイトコードは呼び出しハンドラ内で 関数のオブジェクト ID によって識別されます。これによって、確実に DROP/CREATE といった流れによる関数の変更を新しくデータベースに接 続すること無く反映できます。

関数で使われるすべての式と SQL 文に対して、 PL/pgSQL バイトコードのインタプリタは SPI マネージャの SPI_prepare() と SPI_saveplan() 関数を使って遂行可能な実行計画の準 備を作成 します。これは、PL/pgSQL 関数内で、初めてその文が処理された時に行 なわれます。従って、実行計画用に必要となる文を多く含む、条件付のコ ードを持った関数は、そのデータベース接続が有効である期間に実際に使 われた部分についての計画のみが作成され、保存されます。

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

説明

PL/pgSQL の構造

PL/pgSQL 言語は大文字小文字の区別をしません。全てのキーワード と識別子は大文字小文字を混ぜて使う事ができます。

PL/pgSQLはブロック指向の言語です。ブロックは次のように定義されます。

    [<<label>>]
    [DECLARE
        宣言]
    BEGIN
        
    END;
ブロックの文節には副ブロックを複数持つことができます。副ブロックは そのブロックの外部の文から変数を隠蔽することに使用できます。ブロッ クの前の宣言節で宣言された変数は、関数呼び出しの度ではなく、そのブ ロックに処理が進む時に毎回そのデフォルト値に初期化されます。

文をまとめるための PL/pgSQL 内の BEGIN/END とトランザクションを制 御するデータベースコマンドである BEGIN/END の意味を取り違えてはい けません。関数とトリガプロシージャはトランザクションを始めたり コミットしたりできませんし、Postgres は 入れ子になったトランザクションを持ちません。

コメント

PL/pgSQL には 2 種類のコメントがあります。二重ダッシュ '--' はその 文の終りまでをコメントとするコメントの始まりです。'/*' は次に '*/' が現れるまでのブロック型のコメントの始まりを示します。ブロック型の コメントは入れ子にはできませんが、二重ダッシュのコメントをブロック 型のコメントの内側におくことはでき、また、二重ダッシュを使って、コ メントを区切る '/*' と '*/' を無効にすることもできます。

宣言

FOR ループにて整数値の範囲内を繰り返すループ変数を除き、ブロック またはそのブロックの副ブロックで使われる全ての変数、行、レコード はブロックの宣言節にて宣言されなければいけません。PL/pgSQL 関数へ 与えられるパラメータは自動的にいつもの $n 識別子として宣言されま す。宣言は次のような構文を持ちます。

変数名 [ CONSTANT ] [ NOT NULL ] [ DEFAULT | := 既定値 ];

基本型を指定した変数の宣言です。変数が CONSTANT を付けて宣言された 場合は、その値を変更することはできません。NOT NULL を付けた場合は、 NULL 値を代入しようとすると実行時エラーとなります。全ての変数のデフ ォルト値は SQL の NULL 値ですので、NOT NULL を付 けて宣言した全ての変数はデフォルト値を指定しなければいけません。

デフォルト値は関数が呼ばれる度に評価されます。ですので、 'now' を datetime 型の変数に代入すると、関数が そのバイトコードにプレコンパイルされた時ではなく、実際の関数が呼 び出された時刻をその変数が持つことになります。

変数名 クラス%ROWTYPE;

指定クラスの構造をもった行の宣言です。クラスはデータベース内の既 存のテーブルまたはビューの名前でなければいけません。行のフィール ドはドット記述方法でアクセスされます。関数へのパラメータは複合型( 完全なテーブルの行)でも構いません。この場合、対応する識別子 $n は rowtype になりますが、後述の ALIAS コマンドを使って別名を付けな ければいけません。テーブルの行の内、ユーザ用の行のみがアクセス可能 で、Oid やその他のシステム用の属性にはアクセスできません。(そのた めビューからの属性も可能です。ビューの行は有効なシステム属性を持ち ません。)

rowtype のフィールドは、そのテーブルのフィールドの大きさや、char() などのデータ型の精度を継承します。

変数名 RECORD;

レコードは rowtype に似ていますが、事前に定義された構造を持ちませ ん。SELECT 操作で得た実際のデータベースの行を保持するための選択と FOR ループで使われます。保持しているものとその同じレコードは異なった 選択で使用できます。実際の行が無い時に、そのレコードへのアクセスや レコードのフィールドへの値の代入の試行は実行時エラーになります。

トリガにおける NEW と OLD 行はプロシージャにレコードとして与え られます。Postgres 内では、あるトリガ プロシージャとそれと同じトリガプロシージャは異なるテーブルの トリガイベントを扱うことができるために、これが必要です。

name ALIAS FOR $n;

コードをより読み易くするために、関数への位置パラメータに別名を定義 することができます。

この別名付与は複合型を関数への引数とする時には必須です。SQL 関数内 での$1.salaryといったドット表記方法は PL/pgSQL では許されません。

RENAME oldname TO newname;

変数、レコード、または、行の名前を変更します。これは、トリガプ ロシージャ内で NEW または OLD を別の名前で参照すべき時に有益です。

Data Types

変数の型はデータベース内の既存の基本型のいずれかを取ることができ ます。上述の宣言節内の は次のように 定義されます。

  • Postgres-基本型

  • 変数名%TYPE

  • クラス名.フィールド名%TYPE

変数名 は、同一関数内で前に定義され、こ の段階で参照できる、変数の名前です。

クラス名 は、既存のテーブル又はビューの 名前を、フィールド名 は属性の名前を示し ます。

クラス名.フィールド名%TYPE を使う場合、 バックエンドが動作している間に初めて関数が呼び出された時、PL/pgSQL は属性定義を検索します。char(20) 属性を持ったテーブルと、ローカル な変数内でその内容を取り扱ういくつかの PL/pgSQL 関数があるものと仮 定します。ここで誰かが char(20) では十分でないと決め、テーブルをダ ンプし、それをドロップし、問題の属性を char(40) として定義した属性 で再度作成し、データをレストアします。はぁ、この人は関数の事を忘れ ています。関数内の演算は値を 20 文字で切り詰めてしまいます。しかし、 もし関数にて クラス名.フィールド名%TYPE 宣言が使われていた場合は、もしその属性を text 型で定義した新しいテ ーブルスキーマであったとしても、自動的に魔法がかかったように大きさ の変更を取り扱います。

PL/pgSQL 文で使われる式は全てバックエンドエクゼキュータを使用して 処理されます。定数を含んだ式は実際には実行時に評価される必要がある 場合もあります(例えば日付型の 'now' )ので、PL/pgSQL パーサによ って NULL キーワード以外の定数値を識別することはできません。全ての 式は、内部的に次の問い合わせを SPI マネージャを使って実行すること によって評価されます。

    SELECT 
    
式の中では、変数の識別子の場所はパラメータに置き換えられ、パラメー タ配列内の変数の実際の値はエクゼキュータに渡されます。PL/pgSQL 関数 内で使われる全ての式は一回だけ準備され保存されます。

Postgres 主パーサによって行なわれる型 チェックは定数値の解釈にいくつかの副作用があります。詳しくい うと、次の

    CREATE FUNCTION logfunc1 (text) RETURNS datetime AS '
        DECLARE
            logtxt ALIAS FOR $1;
        BEGIN
            INSERT INTO logtable VALUES (logtxt, ''now'');
            RETURN ''now'';
        END;
    ' LANGUAGE 'plpgsql';
    
    CREATE FUNCTION logfunc2 (text) RETURNS datetime AS '
        DECLARE
            logtxt ALIAS FOR $1;
            curtime datetime;
        BEGIN
            curtime := ''now'';
            INSERT INTO logtable VALUES (logtxt, curtime);
            RETURN curtime;
        END;
    ' LANGUAGE 'plpgsql';
    
という2つの関数が行なうことには違いがあります。logfunc1() の場合、 Postgres 主パーサは、INSERT 用の計画を 準備する時に、logtable フィールドの対象フィールドが datetime 型で あるので、'now' という文字列は datetime 型として解釈すべきであるこ とを判断します。従って、この段階で定数を作成し、バックエンドの生存 期間の間の logfunc1() の全ての呼び出しにおいて、この定数値が使用さ れることになります。いうまでもないことですが、これはプログラマが 予定したものではありません。

logfunc2() の場合、Postgres 主パーサは、 'now' の型を何にすべきかを判断できませんので、'now' という文字列を 持った text データ型を返します。ローカル変数 curtime に代入する時 に、PL/pgSQL インタプリタは、変換用に text_out() と datetime_in() 関数を呼び出して、この文字列を datetime 型にキャストします。

この Postgres 主パーサによってなされる 型チェックは、PL/pgSQLがほとんどできた後に実装されました。6.3 と 6.4 では違いがあり、SPI マネージャの計画準備機能を使用する全ての関 数に影響を与えます。上の方法でローカル変数を使うことが、PL/pgSQL でこれらの値を正しく解釈させる、現時点で唯一の方法です。

式や文でレコードフィールドを使用する場合は、そのフィールドのデータ 型をそれと同一の式との間で変更すべきではありません。1 つ以上のテー ブルのイベントを扱うトリガプロシージャを記述する時には気を付けて 下さい。

下で指定された PL/pgSQL パーサによって理解されないものは全て、問 い合わせ内にしまわれ、実行するためにデータベースエンジンに送られ ます。問い合わせの結果はデータを全く返しません。

代入

変数や行/レコードフィールドへの値の代入は次のように記述されます。

    識別子 := ;
    
式の結果のデータ型が変数のデータ型と合わなかった場合、または、変数 が( char(20) といった)サイズ/精度を持つことが分かっていた場合、 PL/pgSQL バイトコードインタプリタが結果の型の出力関数と変数の型の入 力関数を使用することによって、結果の値は暗黙的にキャストされます。 型の入力関数によって生成される実行時エラーの元になることに注意して 下さい。

選択全体をレコードや行に代入することは、

    SELECT  INTO 対象 FROM ...;
    
によってなされます。対象 はレコード、 行変数、または、変数、レコードフィールド、行フィールドのコン マで区切られたリストです。

行または変数のリストが対象として使われた場合、選択された値は対象 の構造に正確にあっていなければいけません。さもなくば実行時エラーが 発生します。FROM キーワードの後には、SELECT 文で指定できる、修飾、 グループ化、ソートなど有効なものを付けることができます。

SELECT INTO 後に代入が成功したかどうかを即座に点検するために使われ る FOUND という名前のブール型の特別な変数があります。

    SELECT * INTO myrec FROM EMP WHERE empname = myname;
    IF NOT FOUND THEN
        RAISE EXCEPTION ''employee % not found'', myname;
    END IF;
    
選択した結果複数文が返った場合、最初のもののみが対象フィールドに移動 されます。他の全ては静かに処分されます。

他の関数の呼び出し

Prostgres データベース内に定義された全 ての関数は値を返します。従って、関数の通常の呼び出し方法は、SELECT を実行することか(PL/pgSQL 内部で SELECT を実行した結果で ある)代入を行なうことです。しかし、関数の結果に興味が無い場合もあ ります。

    PERFORM 問い合わせ
    
は SPI マネージャを通して 'SELECT 問い合わせ' を実行し、その結果を処分します。ローカル変数といった識別子はパラメ ータに置換されます。

関数からの復帰

    RETURN 
    
関数は終了し、 の値は上位のエクゼキュー タに返されます。関数の戻り値を未定義にすることはできません。制御が RETURN 文に到達せずに関数の最上位ブロックの終りに達した場合、実行時 エラーが発生します。

式の結果は自動的に、代入用に記述された関数の戻り値の型にキャストされ ます。

中断とメッセージ

上の例で示したように、Postgres の elog 機構にメッセージを送る RAISE 文があります。

    RAISE レベル ''フォーマット'' [, 識別子 [...]];
    
フォーマット内の "%" はその後のコンマで区切られた識別 子用の場所を確保するものとして使われます。取り得るレベルは DEBUG (実行中のデータベースへの出力は静かに抑えられます。)、NOTICE (デ ータベースログに出力され、クライアントアプリケーションにも転送され ます。)、EXCEPTION(データベースログに出力されトランザクションを中 断します。)です。

条件

    IF  THEN
        
    [ELSE
        ]
    END IF;
    
は最低でもブール型にキャストできる 値を返す必要があります。

ループ

ループには複数の種類があります。

    [<<ラベル>>]
    LOOP
        
    END LOOP;
    
条件の無いループ、この場合 EXIT 文によって明示的に終了させなければ いけません。ラベルオプションは、入れ子になったループの EXIT 文で、 どのループを終了させるかを指定するために使うことができます。
    [<<ラベル>>]
    WHILE  LOOP
        
    END LOOP;
    
の評価が真である間実行される条件付の ループ。
    [<<ラベル>>]
    FOR 変数名 IN [ REVERSE ]  ..  LOOP
        
    END LOOP;
    
整数値の範囲内で繰り返すループ。変数名 という変数は整数型として自動的に作成され、ループの内側のみで有効で す。範囲の下限と上限を与える 2 つの式はループに入った時にのみ評価さ れます。繰り返しの刻みは常に 1 です。
    [<<ラベル>>]
    FOR レコード | 行 IN select 句 LOOP
        
    END LOOP;
    
レコードまたは行に select 句の結果である全ての行を代入し、それ ぞれについて文を実行します。ループが EXIT 文によって終了した場合、 最後に代入された行はループの後でも参照できます。
    EXIT [ ラベル ] [ WHEN  ];
    
ラベル が指定されていない場合は、最も内 側のループを終了し、END LOOP の後の文が次に実行されます。 ラベル が指定されている場合は、それは現在 の、または、入れ子になったループブロックの上位レベルのラベル名でなけ ればいけません。その名前のループまたはブロックは終了され、制御はその ループ/ブロックに対応する END の後の文に移動します。

トリガプロシージャ

PL/pgSQL はトリガプロシージャの定義に使用できます。トリガプロ シージャは、普通の CREATE FUNCTION コマンドを使って、引数無し、 OPAQUE という戻り値の型を持った関数として作成されます。

トリガプロシージャとして使われる関数には Postgres 特有なことがいくつかあります。

まず、最上位ブロックの宣言節に自動的に特別な変数がいくつか作成され ることです。次のものがあります。

NEW

レコードデータ型。この変数は、行レベルのトリガでの INSERT/UPDATE 操作によって更新されたデータベースの行を保持します。

OLD

レコードデータ型。この変数は、行レベルのトリガでの INSERT/UPDATE 操作によって更新される前のデータベースの行を保持します。

TG_NAME

name データ型。実際に発行されたトリガの名前を持つ変数。

TG_WHEN

text データ型。トリガの定義に依存した 'BEFORE' または 'AFTER' と いう文字列。

TG_LEVEL

text データ型。トリガの定義に依存した 'ROW' または 'STATEMENT' という文字列。

TG_OP

text データ型。トリガを発行した操作を示す 'INSERT'、'UPDATE'、また は、'DELETE' という文字列。

TG_RELID

oid データ型。このトリガの呼び出し元になるテーブルのオブジェク ト ID。

TG_RELNAME

name データ型。このトリガの呼び出し元になるテーブルの名前。

TG_NARGS

整数型。CREATE TRIGGER 文におけるトリガプロシージャに与えられる 引数の数。

TG_ARGV[]

text 型の配列型。CREATE TRIGGER文での引数。このインデックスは 0 か ら始まり、また、インデックスを式で与えることもできます。無効なイン デックス( < 0 or >= tg_nargs )は NULL 値という結果になります。

次は、NULL または、トリガの発行元になったテーブルの構造を正確に 持ったレコード/行を返さなければならない点です。AFTER として発行 されたトリガは、常に、何も影響しない NULL を返すでしょう。BEFORE として発行されたトリガが NULL を返す場合は、トリガマネージャ に実際の行への操作を取り止めるように通知します。一方、レコード/行 を返す場合は、その操作で挿入/更新された行をその戻り値に置き換えま す。NEW の個々の値を直接置き換え、その NEW を返すことも、新しいレ コード/行を完全に作成して返すことも可能です。

例外

Postgres の例外を扱う方式はあまり気のき いたものではありません。パーサ、プランナ、オプティマイザ、または、 エクゼキュータがこれ以上文を実行することができないと判断すると、ト ランザクション全体を中断し、システムは、クライアントアプリケーショ ンからの次の問い合わせを受け付けるメインループに戻ります。

これが発生したことを通知するために、エラー機構に引っかけることができ ます。しかし現時点では、(入出力変換エラー、浮動小数点エラー、分析エ ラーといったもののうち)何がその中断を引き起こしたのかを通知すること はできません。そして、この段階で、上位のエクゼキュータに戻したり、デ ータベース全体を壊してしまうかもしれないようなコマンドを更に発行させ たりして、データベースバックエンドを矛盾のある状態にさせることもでき ます。また、トランザクションが中断されたとはいえ、この段階で情報はク ライアントアプリケーションに送られていますので、操作の再実行は意味が ありません。

従って、現段階で、関数やトリガプロシージャ実行中に中断が起きた時 に PL/pgSQL ができることは、どの関数のどこ(行番号と文の型)で発生 したのかを知らせるといった追加的な DEBUG レベルのログメッセージを出 力することのみです。

PL/pgSQL 関数を記述することが簡単であることを示す数少ない関数をここに 示します。もっと複雑な例については、プログラマの方は PL/pgSQL 用のレ グレッションテストを参照して下さい。

PL/pgSQL 関数の記述での辛いところは、シングルクォートの扱いです。 CREATE FUNCTION 上の関数のソーステキストはリテラルな文字列である必要が あります。リテラルな文字列内でのシングルクォートは二重にまたはバックス ラッシュでクォートしなければいけません。もっと優雅な方法を探しています。 当面は、下の例のように二重にしたシングルクォートを使うべきです。 Postgres の将来のバージョンでのこの問題に対 する解決方法は上位互換になるでしょう。

数点の簡単な PL/pgSQL 関数

次の2つの PL/pgSQL 関数は、C 言語関数の説明での対応するものと同じ ものです。

    CREATE FUNCTION add_one (int4) RETURNS int4 AS '
        BEGIN
            RETURN $1 + 1;
        END;
    ' LANGUAGE 'plpgsql';
    
    CREATE FUNCTION concat_text (text, text) RETURNS text AS '
        BEGIN
            RETURN $1 || $2;
        END;
    ' LANGUAGE 'plpgsql';
    

複合型を使った PL/pgSQL 関数

この PL/pgSQL も C 関数での例と同じです。

    CREATE FUNCTION c_overpaid (EMP, int4) RETURNS bool AS '
        DECLARE
            emprec ALIAS FOR $1;
            sallim ALIAS FOR $2;
        BEGIN
            IF emprec.salary ISNULL THEN
                RETURN ''f'';
            END IF;
            RETURN emprec.salary > sallim;
        END;
    ' LANGUAGE 'plpgsql';
    

PL/pgSQL トリガプロシージャ

このトリガは、テーブルの行が挿入または更新された時には必ず、現 在のユーザ名と時刻がその行に入っていることを確実にします。そして、 従業員名が与えられていることとその給料が正の値であることを確実に します。


    CREATE TABLE emp (
        empname text,
        salary int4,
        last_date datetime,
        last_user name);

    CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS
        BEGIN
            -- Check that empname and salary are given
            IF NEW.empname ISNULL THEN
                RAISE EXCEPTION ''empname cannot be NULL value'';
            END IF;
            IF NEW.salary ISNULL THEN
                RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
            END IF;

            -- Who works for us when she must pay for?
            IF NEW.salary < 0 THEN
                RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
            END IF;

            -- Remember who changed the payroll when
            NEW.last_date := ''now'';
            NEW.last_user := getpgusername();
            RETURN NEW;
        END;
    ' LANGUAGE 'plpgsql';

    CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
        FOR EACH ROW EXECUTE PROCEDURE emp_stamp();