本節では、PL/pgSQL利用者の知識として重要な、実装の詳細を述べます。
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
はテーブル名でなければなりません。
また、col
はdest
の列でなければなりませんが、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.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
コマンドまたはその亜種におけるコマンド文字列の中では起こりません。
そのようなコマンドに可変値を挿入する時は、43.5.4に述べたように、文字列の値を構成するものの一部とするかUSING
を使用してください。
今のところ変数置換は、SELECT
とINSERT
とUPDATE
とDELETE
コマンドと(EXPLAIN
やCREATE TABLE ... AS SELECT
のような)このうちの1つを含むコマンドの中だけで作動します。
メインSQLエンジンが問い合わせパラメータをこれらのコマンドでしか許可しないからです。
他の種類の文(通常ユーティリティ文といいます)において可変名または可変値を使用するには、文字列としてユーティリティ文を構成しEXECUTE
してください。
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インタプリタはこの文字列をtextout
とtimestamp_in
関数を変換に使用してtimestamp
型にキャストします。
ですから、演算されたタイムスタンプは、プログラマが意図した通り、実行の度に更新されます。
この方法でたまたま意図した通り動くけれど、それほど効率的ではありません。
ですから、now()
関数の使用の方が優れています。