★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 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

43.11. PL/pgSQLの秘訣

本節では、PL/pgSQL利用者の知識として重要な、実装の詳細を述べます。

43.11.1. 変数置換

PL/pgSQL関数内のSQL文および式は変数および関数のパラメータを参照することができます。 背後では、PL/pgSQLはこうした参照を問い合わせパラメータに置き換えます。 文法的に許されているところでのみ問い合わせパラメータは置換されます。 極端な場合として、以下のよろしくないプログラミングスタイルの例を考えてみましょう。

INSERT INTO foo (foo) VALUES (foo(foo));

最初に現れるfooの場所は文法的にはテーブル名でなければなりません。 このため関数がfooという名前の変数を持っていたとしても、置換されません。 2番目の場所はそのテーブルの列名でなければなりません。このためこれも置換されません。 同様に、3番目の場所は関数名でなければなりません。このためこれも置換されません。 最後の場所のみがPL/pgSQLの関数の変数参照の候補です。

これを理解する別の方法は、変数の置換はSQLコマンドへデータ値を挿入できるだけだということです。コマンドが参照するデータベースオブジェクトを動的には変更できません。 (そのようにしたければ、43.5.4に書かれているように、コマンド文字列を動的に構成しなければなりません。)

変数名は文法的にはテーブル列名と違いがありませんので、テーブルを参照する文の中であいまいさが出る可能性があります。 与えられた名前はテーブル列を意味するのでしょうか、それとも変数なのでしょうか。 前の例を次のように変えてみましょう。

INSERT INTO dest (col) SELECT foo + bar FROM src;

ここでは、destおよびsrcはテーブル名でなければなりません。 また、coldestの列でなければなりませんが、fooおよびbarは理論上関数の変数かもしれませんし、srcの列かもしれません。

デフォルトでPL/pgSQLはSQL文における名前が変数かテーブル列のいずれかを参照可能な場合にエラーを報告します。 変数または列の名前を変更することやあいまいな参照を修飾すること、PL/pgSQLにどちらを優先して解釈するかを通知することで、こうした問題を解消することができます。

最も簡単な解法は変数名または列名を変更することです。 一般的なコーディング法として、列の命名とPL/pgSQL変数の命名とで規約を分ける方法があります。 例えば、一貫して関数の変数はv_somethingという名前とし、列名はv_で始まらないようにすれば、競合は起こりません。

その他、あいまいな参照を明確にするために修飾することができます。 上の例では、src.fooによりテーブル列への参照についてあいまいさが解消します。 あいまい性のない変数参照を行うためには、ラベル付けしたブロック内で変数を宣言し、そのブロックのラベルを使用します(43.2参照)。 以下に例を示します。

<<block>>
DECLARE
    foo int;
BEGIN
    foo := ...;
    INSERT INTO dest (col) SELECT block.foo + bar FROM src;

ここでblock.foosrcfoo列があったとしても、変数を意味することになります。 FOUNDなどの特別な変数を含め、関数パラメータを関数名で修飾することができます。 これらは暗黙的に関数名をラベル名とした上位ブロック内で宣言されているためです。

PL/pgSQLの大規模な本体コードにおける、すべてのあいまいな参照を修正することが現実的ではない場合があります。 こうした場合、PL/pgSQLにあいまいな参照を変数として解決すべき(この動作はPostgreSQL 9.0より前のPL/pgSQLの動作と互換性を持ちます)、または、テーブル列参照として解決すべき(Oracleなどの他のシステムと互換性を持ちます)と指定することができます。

システム全体に対してこの動作を変更するためにはplpgsql.variable_conflict設定パラメータをerroruse_variableuse_columnのいずれかに設定します(errorが標準配布におけるデフォルトです)。 このパラメータは以降のPL/pgSQL関数の文のコンパイルに影響しますが、現在のセッションでコンパイル済みの文には影響を与えません。 この設定を変更することで、PL/pgSQLの動作において予期できない変化が発生することがありますので、これはスーパーユーザのみが変更することができます。

また、関数テキストの先頭に以下の特殊なコマンドの1つをいれることで、関数単位で動作を設定することもできます。

#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

これらのコマンドを記述した関数に対してのみ、コマンドは影響を与え、plpgsql.variable_conflictの設定を上書きします。 以下に例を示します。

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    #variable_conflict use_variable
    DECLARE
        curtime timestamp := now();
    BEGIN
        UPDATE users SET last_modified = curtime, comment = comment
          WHERE users.id = id;
    END;
$$ LANGUAGE plpgsql;

UPDATEコマンドにおいて、curtimecommentおよびidは、usersに同名の列があるか否かに関わらず、関数の変数またはパラメータを参照します。 テーブル列を参照させるためにWHERE句においてusers.idと参照を修飾する必要があったことに注意して下さい。 しかしUPDATEリストの対象としてのcommentへの参照は修飾させる必要がありませんでした。 これは文法的にusersの列でなければならないためです。 以下のようにvariable_conflictの設定に依存せずに同じ関数を作成することもできます。

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    <<fn>>
    DECLARE
        curtime timestamp := now();
    BEGIN
        UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment
          WHERE users.id = stamp_user.id;
    END;
$$ LANGUAGE plpgsql;

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

今のところ変数置換は、SELECTINSERTUPDATEDELETEコマンドと(EXPLAINCREATE TABLE ... AS SELECTのような)このうちの1つを含むコマンドの中だけで作動します。 メインSQLエンジンが問い合わせパラメータをこれらのコマンドでしか許可しないからです。 他の種類の文(通常ユーティリティ文といいます)において可変名または可変値を使用するには、文字列としてユーティリティ文を構成しEXECUTEしてください。

43.11.2. 計画のキャッシュ

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

各式やSQLコマンドが初めてその関数で実行される時に、PL/pgSQLインタプリタはSPIマネージャのSPI_prepare関数を使用して、プリペアドステートメントを作成するためにコマンドを解析します。 その後にその式やコマンドが行われる時には、そのプリペアドステートメントを再利用します。 こうして、めったに分岐されない条件付きコードパスを持つ関数では、現在のセッションで実行されないそれらのコマンドの解析によるオーバーヘッドを背負いこむことはありません。 欠点は特定の式や問い合わせのエラーが、関数の該当部分が実行されるまで検出されないことです。 (典型的な構文エラーは、最初の解釈において検出されますが、それより深いエラーは、実行の時まで検出されません)。

PL/pgSQLは(正確にはSPIマネージャは)さらに特定のプリペアドステートメントに関する実行計画のキャッシュを試行できます。 キャッシュした実行計画が使用されなかった場合、プリペアドステートメントが呼び出される度に新しい実行計画が作成され、選択した実行計画を最適にするために、最新のパラメータ値(すなわちPL/pgSQLの変数値)が使用されます。 プリペアドステートメントがパラメータを持たないか何回も使用される場合、SPIマネージャは特定のパラメータ値に依存しない一般的な実行計画の作成を考え、再使用のためにキャッシュします。 典型的には、これは参照したPL/pgSQLの変数値が、実行計画にさほど影響しない場合にだけ起こります。 それならば、毎回の実行計画の作成の方が優れています。 プリペアドステートメントに関する詳細はPREPAREを参照してください。

このようにPL/pgSQLはプリペアドステートメントおよび時には実行計画を保存しますので、PL/pgSQL関数内に直接現れるSQLコマンドは実行の度に同じテーブルとフィールドを参照しなければなりません。 つまり、SQLコマンドにて、テーブルやフィールドの名前としてパラメータを使用することができません。 実行の度に新しく実行計画を作成して解析する無駄を覚悟で、PL/pgSQLEXECUTE文を使った動的問い合わせを構成することで、この制限を回避できます。

レコード変数の変わりやすいという性質はこの接続において別の問題となります。 レコード変数のフィールドが式や文の中で使用される場合、そのフィールドのデータ型を関数を呼び出す度に変更してはいけません。 それぞれの式が最初に実行された時のデータ型を使用して、その式が解析されているからです。 必要な場合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'timestamp定数に変換し、その定数値をその後のセッションの有効期間におけるlogfunc1の全ての呼び出しで使用します。 言うまでもありませんが、これはプログラマが意図した動作ではありません。 now()またはcurrent_timestamp関数の使用が優れています。

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