38.10. PL/pgSQLの秘訣

38.10.1. 変数置換

PL/pgSQLが SQL を実行する文または式を準備する時、文または式に現れるPL/pgSQL変数名は$nで表現されるパラメータ記号に置き換わります。 文または式が実行されるたびに、その時点の変数値がパラメータの値として供給されます。 例として、以下の関数を考えます。

CREATE FUNCTION logfunc(logtxt text) RETURNS void AS $$
    DECLARE
        curtime timestamp := now();
    BEGIN
        INSERT INTO logtable VALUES (logtxt, curtime);
    END;
$$ LANGUAGE plpgsql;

上のINSERT文は、EXECUTEが実行される時点の 2変数 $1と $2の値によって、下のように処理されます。

PREPARE statement_name(text, timestamp) AS
  INSERT INTO logtable VALUES ($1, $2);

（ここで述べているのは、主 SQL エンジンEXECUTEのEXECUTEであり、PL/pgSQLのEXECUTEでないことに注意してください）。

置換の仕組みでは、既知の変数名と一致する全てのトークンを置き換えます。 そのため、不注意によるいろいろな落とし穴があります。 例えば、変数名が関数内の問い合わせで参照するテーブル名または列名と一致することは不適です。 なぜなら、テーブル名または列名であろうとも、置換されるからです。 上例におけるlogtableテーブルにlogtxt列とlogtime列がある時、以下のINSERTを記述すると、

        INSERT INTO logtable (logtxt, logtime) VALUES (logtxt, curtime);

SQL パーサは次のように置換するため、

        INSERT INTO logtable ($1, logtime) VALUES ($1, $2);

下のような構文エラーとなります。

ERROR:  syntax error at or near "$1"
LINE 1: INSERT INTO logtable ( $1 , logtime) VALUES ( $1 ,  $2 )
                               ^
QUERY:  INSERT INTO logtable ( $1 , logtime) VALUES ( $1 ,  $2 )
CONTEXT:  SQL statement in PL/PgSQL function "logfunc2" near line 5

明らかな構文エラーとなるため、上例はかなり解析しやすいものです。 とても厄介なものは、置換においては正常な構文と判定されるが、実行時に関数の不適切な処理となるものです。 以下に一例を示します。

    DECLARE
        val text;
        search_key integer;
    BEGIN
        ...
        FOR val IN SELECT val FROM table WHERE key = search_key LOOP ...

上のように記述したとすると、利用者はなぜテーブルがNULLのように見えるかと疑問に思うことになるでしょう。 分かるかと思いますが、この問い合わせは以下のようになってしまいます。

        SELECT $1 FROM table WHERE key = $2

この結果、この問い合わせは各行に対し、単にvalの現在値を自身に無駄に代入しているだけになります。

この落とし穴を避けるためのコーディング法として、テーブルと列の命名とPL/pgSQL変数の命名を分ける方法があります。 例えば、PL/pgSQL変数はv_somethingという名前とし、テーブル名と列名はv_で始まらないようにすれば、かなり安全です。

別の方法は、SQL に修飾した（ドットを付けた）列名を使用するものです。 前の例において、以下のように記述すれば安全です。

        FOR val IN SELECT table.val FROM table WHERE key = search_key LOOP ...

ドットの後の部分は、PL/pgSQLが変数に置換しないからです。 しかし、この方法が常に成功するとは限りません。 例えば、INSERTにはドットを付けた列名を使用できません。 また、レコード変数と行変数の名前は、ドットの前の部分と一致することがあるため、修飾した列名が危険な場合もあります。 その時は、変数名と衝突させないことが唯一の方法です。

使用できる別の方法は、変数を宣言するブロックにラベルを付加するものです。 SQL コマンドにはラベル付きの変数名を使用して、PL/pgSQL変数と区別します（項38.2を見てください）。 以下に例を示します。

    <<pl>>
    DECLARE
        val text;
    BEGIN
        ...
        UPDATE table SET col = pl.val WHERE ...

SQL コマンド中のドットを付けない列名が"誤った"変換をされる危険があるため、この方法自身は名前が衝突する問題の解法ではありません。 あいまいとなる可能性のあるコードの意味を、明確化することには有用です。

変数置換はEXECUTEコマンドまたはその変形における文字列の中では起こりません。 そのようなコマンドに可変値を挿入する時は、項38.5.4に述べたように、文字列の値を構成するものの一部としてください。

今のところ変数置換は、SELECTとINSERTとUPDATEとDELETEコマンドの中だけで作動します。 主 SQL エンジンがパラメータのシンボルをこれらのコマンドでしか許可しないからです。 他の文型（通常ユーティリティ文といいます）において可変名または可変値を使用するには、文字列としてユーティリティ文を構成しEXECUTEしてください。

38.10.2. 計画のキャッシュ

PL/pgSQLインタプリタは、初めてその関数が（各セッションで）呼び出された時に、関数のソーステキストを解析し、バイナリ形式の命令ツリーを内部で作成します。 この命令ツリーは完全にPL/pgSQL文構造に変換されますが、関数内部の個々のSQL式とSQLコマンドは即座に変換されません。

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

上に述べたようにPL/pgSQL変数値を使用するたびに変更できるために、準備した計画はパラメータ化されることがその他の重要な点です。 この意味は、特定の変数値のために作成する時より、計画の効率が悪くなる場合があるということです。 以下の例において、

SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term;

search_termがPL/pgSQL変数とします。 この問い合わせ計画のキャッシュが、wordのインデックスを使用することはありません。 というのは、プランナは実行時におけるLIKEパタンが固定したものだと仮定できないからです。 インデックスを使用するためには、LIKEパタンが特定の定数である問い合わせを計画しなければなりません。 この場合も、EXECUTEが実行ごとに作成された新規の計画を使用できます。

レコード変数の変わりやすいという性質はこの接続において別の問題となります。 レコード変数のフィールドが式や文の中で使用される場合、そのフィールドのデータ型を関数を呼び出す度に変更してはいけません。 各々の式が最初に実行された時のデータ型を使用して、その式の計画が作成されているからです。 必要な場合EXECUTEを使用してこの問題を回避することができます。

同一の関数が2つ以上のテーブルのトリガとして使用される場合、PL/pgSQLはテーブルごとの計画を準備してキャッシュします。 すなわち、各々のトリガ関数とテーブルの組ごとにキャッシュするのであり、トリガ関数ごとではありません。 このため、データ型の変更に伴う問題の一部を軽減します。 例えば、別のテーブルにある異なったデータ型であっても、keyと命名した列に対してトリガ関数は有効に作動します。

同様に、多相型の引数を持った関数は、実際に呼び出す引数の型の組み合わせごとに別々の計画をキャッシュします。 そのため、データ型の差異が原因で予期しない失敗が起こることはありません。

計画のキャッシュにより、時間に依存する値の解釈の結果に違いが現れることがあります。 例えば、以下の2つの関数の結果は異なります。

CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
    BEGIN
        INSERT INTO logtable VALUES (logtxt, 'now');
    END;
$$ LANGUAGE plpgsql;

および

CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
    DECLARE
        curtime timestamp;
    BEGIN
        curtime := 'now';
        INSERT INTO logtable VALUES (logtxt, curtime);
    END;
$$ LANGUAGE plpgsql;

logfunc1の場合では、PostgreSQLメインパーサは、INSERT用の計画を準備する時に、logtableの対象列の型から'now'をtimestampと解釈しなければならないことを把握しています。 こうして、パーサはINSERTが計画された時点で'now'を定数に変換し、その定数値をその後のセッションの有効期間におけるlogfunc1の全ての呼び出しで使用します。 言うまでもありませんが、これはプログラマが意図した動作ではありません。

logfunc2の場合では、PostgreSQLメインパーサは'now'の型を決定することができません。 そのため、nowという文字列を持つtext型のデータ値を返します。 curtimeローカル変数に代入する時に、PL/pgSQLインタプリタはこの文字列をtext_outとtimestamp_in関数を変換に使用してtimestamp型にキャストします。 ですから、演算されたタイムスタンプは、プログラマが意図した通り、実行の度に更新されます。