無料スクリプト配布のPHP.TO   PHPの実用的なtips PHPマニュアル MySQLマニュアル Apacheマニュアル PostgreSQLマニュアル マニュアル検索    

40.10. PL/pgSQL の秘訣

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

40.10.1. 変数置換

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

INSERT INTO foo (foo) VALUES (foo);

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

注意: 9.0より前の PostgreSQL では、3つの場合すべてにおいて変数を置換しようとし、構文エラーを引き起こしました。

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

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

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

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

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

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

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

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

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

システム全体に対してこの動作を変更するためには plpgsql.variable_conflict 設定パラメータを error use_variable use_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 コマンドにおいて、 curtime comment および 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 コマンドまたはその亜種におけるコマンド文字列の中では起こりません。 そのようなコマンドに可変値を挿入する時は、 項40.5.4 に述べたように、文字列の値を構成するものの一部とするか USING を使用してください。

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

40.10.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/pgSQL 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' timestamp 定数に変換し、その定数値をその後のセッションの有効期間における logfunc1 の全ての呼び出しで使用します。 言うまでもありませんが、これはプログラマが意図した動作ではありません。 now() または current_timestamp 関数の使用が優れています。

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


powered by SEO.CUG.NET