PL/pgSQL

PL/pgSQL の構造

The PL/pgSQL language is case insensitive. All keywords and identifiers can be used in mixed upper- and lowercase.

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

PL/pgSQL is a block oriented language. A block is defined as

    [<<label>>]
    [DECLARE
        declarations]
    BEGIN
        statements
    END;

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

    [<<label>>]
    [DECLARE
        宣言]
    BEGIN
        文
    END;

It is important not to misunderstand the meaning of BEGIN/END for grouping statements in PL/pgSQL and the database commands for transaction control. Functions and trigger procedures cannot start or commit transactions and Postgres does not have nested transactions.

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

コメント

There are two types of comments in PL/pgSQL. A double dash '--' starts a comment that extends to the end of the line. A '/*' starts a block comment that extends to the next occurence of '*/'. Block comments cannot be nested, but double dash comments can be enclosed into a block comment and a double dash can hide the block comment delimiters '/*' and '*/'.

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

宣言

All variables, rows and records used in a block or it's subblocks must be declared in the declarations section of a block except for the loop variable of a FOR loop iterating over a range of integer values. Parameters given to a PL/pgSQL function are automatically declared with the usual identifiers $n. The declarations have the following syntax:

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

Data Types

The type of a varible can be any of the existing basetypes of the database. type in the declarations section above is defined as:

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

• Postgres-基本型

• 変数名%TYPE

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

variable is the name of a variable, previously declared in the same function, that is visible at this point.

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

class is the name of an existing table or view where field is the name of an attribute.

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

Using the class.field%TYPE causes PL/pgSQL to lookup the attributes definitions at the first call to the funciton during the lifetime of a backend. Have a table with a char(20) attribute and some PL/pgSQL functions that deal with it's content in local variables. Now someone decides that char(20) isn't enough, dumps the table, drops it, recreates it now with the attribute in question defined as char(40) and restores the data. Ha - he forgot about the funcitons. The computations inside them will truncate the values to 20 characters. But if they are defined using the class.field%TYPE declarations, they will automagically handle the size change or if the new table schema defines the attribute as text type.

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

式

All expressions used in PL/pgSQL statements are processed using the backends executor. Expressions which appear to contain constants may in fact require run-time evaluation (e.g. 'now' for the datetime type) so it is impossible for the PL/pgSQL parser to identify real constant values other than the NULL keyword. All expressions are evaluated internally by executing a query

    SELECT expression
    

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

    SELECT 式
    

The type checking done by the Postgres main parser has some side effects to the interpretation of constant values. In detail there is a difference between what the two functions

    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';
    

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';
    

In the case of logfunc2(), the Postgres main parser does not know what type 'now' should become and therefor it returns a datatype of text containing the string 'now'. During the assignment to the local variable curtime, the PL/pgSQL interpreter casts this string to the datetime type by calling the text_out() and datetime_in() functions for the conversion.

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

This type checking done by the Postgres main parser got implemented after PL/pgSQL was nearly done. It is a difference between 6.3 and 6.4 and affects all functions using the prepared plan feature of the SPI manager. Using a local variable in the above manner is currently the only way in PL/pgSQL to get those values interpreted correctly.

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

If record fields are used in expressions or statements, the data types of fields should not change between calls of one and the same expression. Keep this in mind when writing trigger procedures that handle events for more than one table.

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

文

Anything not understood by the PL/pgSQL parser as specified below will be put into a query and sent down to the database engine to execute. The resulting query should not return any data.

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

トリガプロシージャ

PL/pgSQL can be used to define trigger procedures. They are created with the usual CREATE FUNCTION command as a function with no arguments and a return type of OPAQUE.

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

There are some Postgres specific details in functions used as trigger procedures.

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

First they have some special variables created automatically in the toplevel blocks declaration section. They are

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

Second they must return either NULL or a record/row containing exactly the structure of the table the trigger was fired for. Triggers fired AFTER might always return a NULL value with no effect. Triggers fired BEFORE signal the trigger manager to skip the operation for this actual row when returning NULL. Otherwise, the returned record/row replaces the inserted/updated row in the operation. It is possible to replace single values directly in NEW and return that or to build a complete new record/row to return.

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

例外

Postgres does not have a very smart exception handling model. Whenever the parser, planner/optimizer or executor decide that a statement cannot be processed any longer, the whole transaction gets aborted and the system jumps back into the mainloop to get the next query from the client application.

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

It is possible to hook into the error mechanism to notice that this happens. But currently it's impossible to tell what really caused the abort (input/output conversion error, floating point error, parse error). And it is possible that the database backend is in an inconsistent state at this point so returning to the upper executor or issuing more commands might corrupt the whole database. And even if, at this point the information, that the transaction is aborted, is already sent to the client application, so resuming operation does not make any sense.

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

Thus, the only thing PL/pgSQL currently does when it encounters an abort during execution of a function or trigger procedure is to write some additional DEBUG level log messages telling in which function and where (line number and type of statement) this happened.

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

数点の簡単な PL/pgSQL 関数

The following two PL/pgSQL functions are identical to their counterparts from the C language function discussion.

次の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 関数

Again it is the PL/pgSQL equivalent to the example from The C functions.

この 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 トリガプロシージャ

This trigger ensures, that any time a row is inserted or updated in the table, the current username and time are stamped into the row. And it ensures that an employees name is given and that the salary is a positive value.

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

    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();