本節および次節では、明示的にPL/pgSQLで解釈される、全ての種類の文について説明します。 これらの種類の文として認められないものはすべて、SQLコマンドであると仮定され、43.5.2において記述したように、メインデータベースエンジンに送信され実行されます。
値をPL/pgSQL変数に代入する場合は以下のように記述します。
variable
{ := | = }expression
;
上述した通り、このような文中にある式は、メインデータベースエンジンに送信されるSELECT
SQLコマンドによって評価されます。
式は1つの値を生成しなければなりません
(変数が行変数またはレコード変数の場合は行値となるかもしれません)。
対象の変数は単純な変数(ブロック名で修飾可能)、行またはレコードの対象のフィールド、または配列の対象の要素またはスライスとすることができます。
等号(=
)がPL/SQLにおける代入記号(:=
)の代わりに使用できます。
式の結果データ型が変数のデータ型に一致しない場合、値は代入キャスト(10.4を参照)と同様に変換されます。 関係する二つのデータ型のための代入キャストが無いときには、PL/pgSQLインタプリタは結果値を、変数のデータ型の入力関数に続けて結果データ型の出力関数を適用することで、テキストとして変換しようとします。 結果値の文字列形式が入力関数で受け付けることができない場合に、入力関数において実行時エラーが発生するかもしれないことに注意してください。
例:
tax := subtotal * 0.06; my_record.user_id := 20; my_array[j] := 20; my_array[1:3] := array[1,2,3]; complex_array[n].realpart = 12.3;
一般に、行を返さないSQLコマンドはPL/pgSQL関数内にそのコマンドを書くだけで実行されます。 例えば、テーブルを作成してデータを入れるには以下のように書けます。
CREATE TABLE mytable (id int primary key, data text); INSERT INTO mytable VALUES (1,'one'), (2,'two');
コマンドが行を返すのであれば(例えばSELECT
やRETURNING
を伴うINSERT
/UPDATE
/DELETE
など)、処理する方法が2つあります。
コマンドが多くても1行を返す場合、もしくは出力の最初の行だけに関心がある場合には、43.5.3に書かれているように、出力を取得するためのINTO
句を追加する以外は通常通りコマンドを書いてください。
出力行をすべて処理するためには、43.6.6に書かれているように、FOR
ループに対するデータソースとしてコマンドを書いてください。
通常、静的に定義されたSQLコマンドを実行するだけでは十分ではありません。 典型的には、可変のデータ値を使ったり、さらには異なる時には異なるテーブル名を使うなどより基本的な方法で変化したりするコマンドを使いたいでしょう。 今回も、状況に応じて2つの方法があります。
PL/pgSQL変数値は、最適化可能なSQLコマンドに自動的に挿入できます。最適化可能なSQLコマンドとは、SELECT
、INSERT
、UPDATE
、DELETE
、MERGE
とEXPLAIN
やCREATE TABLE ... AS SELECT
のようなこのうちの1つを含む特定のユーティリティコマンドのことです。
このコマンドでは、コマンドテキストに現れるすべてのPL/pgSQL変数名は、問い合わせパラメータで置き換えられ、その後、実行時のパラメータ値として、その時点の変数値が提供されます。
これは以前に述べた式に関する処理と全く同じです。詳細は43.11.1を参照してください。
最適化可能なSQLコマンドがこのように実行されると、43.11.2に記述したように、PL/pgSQLはコマンドのために、実行計画をキャッシュして再利用します。
最適化可能ではないSQLコマンド(ユーティリティコマンドとも呼ばれます)は問い合わせパラメータを受け付けられません。
そのため、PL/pgSQL変数の自動置換はそのようなコマンド内では機能しません。
PL/pgSQLから実行されるユーティリティコマンドに不変ではないテキストを含めるには、43.5.4で述べたように、ユーティリティコマンドを文字列として構築し、それをEXECUTE
しなければなりません。
例えば、テーブル名を変更するなど、データ値を提供する以外の方法でコマンドを修正したい場合にも、EXECUTE
を使わなければなりません。
式またはSELECT
問い合わせを評価して結果を破棄することが、役に立つ場合があります。
例えば、関数の呼び出しにおいて、副次的な成果を取得できるが、結果は無用である場合です。
このような時PL/pgSQLでは、PERFORM
文を使用してください。
PERFORM query
;
これはquery
を実行し、その結果を破棄します。
SQLのSELECT
文と同じ方法でquery
を記述しますが、最初のキーワードSELECT
をPERFORM
に置き換えてください。
WITH
問い合わせに対しては、PERFORM
を使用して、問い合わせをカッコ内に配置してください。
(この場合、問い合わせは1行だけ返すことができます。)
上述のように、PL/pgSQL変数は問い合わせ内に置き換えられ、計画は同様にキャッシュされます。
また、特殊な変数であるFOUND
は問い合わせ結果が1行でも生成された場合は真に設定され、生成されない場合は偽に設定されます(43.5.5を参照してください)。
直接SELECT
を記述すれば、この結果を得ることができると考えるかもしれませんが、現時点でこれを行う方法はPERFORM
しかありません。
SELECT
のように行を返すSQLコマンドは、エラーとして拒絶されます。
なお、INTO
句を有する時は例外であり、次節で説明します。
以下に例を示します。
PERFORM create_mv('cs_session_page_requests_mv', my_query);
(多分、複数列の)1行を返すSQLコマンドの結果は、レコード変数、行型の変数、スカラ変数のリストに代入することができます。
これは、基本的なSQLコマンドを記述して、それにINTO
句を追加することによって行われます。
以下に例を示します。
SELECTselect_expressions
INTO [STRICT]target
FROM ...; INSERT ... RETURNINGexpressions
INTO [STRICT]target
; UPDATE ... RETURNINGexpressions
INTO [STRICT]target
; DELETE ... RETURNINGexpressions
INTO [STRICT]target
;
ここで、target
はレコード変数、行変数、あるいは、単純な変数とレコード/行変数のフィールドをカンマで区切ったリストです。
上述のようにPL/pgSQL変数によりコマンドの残り(すなわち、INTO
句以外のすべて)が置換され、同じように計画がキャッシュされます。
このように作動するのは、SELECT
、RETURNING
を伴ったINSERT
/UPDATE
/DELETE
とおよびEXPLAIN
のような行セットの結果を返す特定のユーティリティコマンドです。
INTO
句以外では、SQLコマンドはPL/pgSQLの外部に記述したものと同じです。
通常のPostgreSQLのSELECT INTO
文では、INTO
の対象は新しく作成されるテーブルです。
しかし、INTO
を伴ったSELECT
では、この解釈が通常と大きく異なることに注意してください。
PL/pgSQL関数内部でSELECT
の結果からテーブルを作成したい場合は、CREATE TABLE ... AS SELECT
構文を使用してください。
行変数または変数リストが対象に使用された場合、列数とデータ型においてコマンドの結果と対象の構造が正確に一致しなければなりません。 さもないと、実行時エラーが発生します。 レコード変数が対象の場合は、コマンドの結果の列の行型に自身を自動的に設定します。
INTO
句はSQLコマンドのほとんど任意の場所に記述することができます。
習慣的には、SELECT
文においてはselect_expressions
の直前または直後に、他のコマンドにおいては文の終わりに記述されます。
将来のバージョンでPL/pgSQLのパーサがより厳格になる場合に備えて、この習慣に従うことを推奨します。
INTO
句においてSTRICT
が指定されない場合、target
はコマンドが返す最初の行となり、コマンドが行を返さない時はNULLとなります。
(「最初の行」とはORDER BY
を使用しないと定義できないことに注意してください。)
2行目以降の行の結果は、全て破棄されます。
以下のように、特殊なFOUND
変数(43.5.5を参照してください)を調べて、行が返されたかどうかを検査することができます。
SELECT * INTO myrec FROM emp WHERE empname = myname; IF NOT FOUND THEN RAISE EXCEPTION 'employee % not found', myname; END IF;
STRICT
オプションが指定された場合、コマンドは正確に1行を返さなければなりません。
さもないと、行がない時はNO_DATA_FOUND
、2行以上が返った時はTOO_MANY_ROWS
という実行時エラーが生じます。
エラーを捕捉したい時は、例外ブロックを使用できます。
以下に例を示します。
BEGIN SELECT * INTO STRICT myrec FROM emp WHERE empname = myname; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'employee % not found', myname; WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION 'employee % not unique', myname; END;
STRICT
を指定したコマンドが成功すると、FOUND
変数は常に真に設定されます。
PL/pgSQLはSTRICT
が指定されない場合でも、RETURNING
を伴ったINSERT
/UPDATE
/DELETE
が2行以上を返した時は、エラーとなります。
なぜなら、どの1行を返すか決定するORDER BY
のようなオプションが存在しないからです。
print_strict_params
が関数に利用可能であり、かつ要求がSTRICT
でないためにエラーが発生した場合、エラーメッセージのDETAIL
部分はコマンドに渡したパラメータに関する情報を含みます。
plpgsql.print_strict_params
を指定することにより、全ての関数のprint_strict_params
設定を変更できます。
しかし、変更後にコンパイルした関数にだけ有効です。
コンパイルオプションを使用すれば、個々の関数を基準とした設定変更もできます。
例を示します。
CREATE FUNCTION get_userid(username text) RETURNS int AS $$ #print_strict_params on DECLARE userid int; BEGIN SELECT users.userid INTO STRICT userid FROM users WHERE users.username = get_userid.username; RETURN userid; END; $$ LANGUAGE plpgsql;
失敗したとき、この関数は次のようなエラーメッセージを生成します。
ERROR: query returned no rows DETAIL: parameters: $1 = 'nosuchuser' CONTEXT: PL/pgSQL function get_userid(text) line 6 at SQL statement
STRICT
オプションは、OracleのPL/SQLのSELECT INTO
および関連した文に対応します。
PL/pgSQL関数の内部で、動的コマンド、つまり実行する度に別のテーブルや別のデータ型を使用するコマンドを生成したいということがよくあるでしょう。
PL/pgSQLが通常行うコマンドの計画のキャッシュは(43.11.2で述べたように)このような状況では動作しません。
この種の問題を扱うために、以下のEXECUTE
文が用意されています。
EXECUTEcommand-string
[ INTO [STRICT]target
] [ USINGexpression
[, ... ] ];
ここで、command-string
は実行されるコマンドを含む(text
型の)文字列を生成する式です。
オプションのtarget
はレコード変数、行変数、あるいは、単純な変数とレコード/行変数のフィールドをカンマで区切ったリストで、その中にコマンドの結果が格納されます。
オプションのUSING
式は コマンドに挿入される値を与えます。
PL/pgSQL変数は、この演算用のコマンド文字列へ置換されません。 必要な変数の値はすべてコマンド文字列を作成する時に埋め込まなければなりません。 もしくは、以下に説明するパラメータを使用することもできます。
また、EXECUTE
を介して実行されるコマンド計画をキャッシュすることはありません。
代わりに、コマンドは文が実行されるとき常に計画されます。
したがって、異なるテーブルと列に対する操作を実行できるように、コマンド文字列を関数内部で動的に作成することができます。
INTO
句は、行を返すSQLコマンドの結果を代入するべき場所を指定します。
行変数または変数リストが用いられる時、それはコマンドの結果の構造と正確に一致しなければなりません。レコード変数が使用される時、自動的に結果の構造と一致するように自身を構築させます。
複数の行が返された時、最初の行だけがINTO
変数に代入されます。
1行も返されない時、NULLがINTO
変数に代入されます。
INTO
句が指定されない時、コマンドの結果は捨てられます。
STRICT
オプションが指定された時、コマンドの結果が正確に1行の場合を除き、エラーとなります。
コマンド文字列はパラメータ値を使用可能で、それらは$1
、$2
等としてコマンドの中で参照されます。
これらの記号はUSING
で与えられる値を参照します。
この方式はデータの値をテキストとしてコマンド文字列の中に挿入する際、よく好まれます。
それは値をテキストに変換、そしてその逆を行う場合の実行時オーバーヘッドを防止するとともに、引用符付けするとか、エスケープをする必要がないため、SQLインジェクション攻撃に対してより襲われにくくなります。
以下に例を示します。
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date;
パラメータ記号はデータ値のみ使用可能です。 もし動的に決定されるテーブルや列名を使用したい場合、テキストでコマンド文字列にそれらを挿入する必要があります。 例えば、先行する問い合わせが、動的に選択されたテーブルに対して処理される必要がある時は、次のようにします。
EXECUTE 'SELECT count(*) FROM ' || quote_ident(tabname) || ' WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date;
よりきれいな方法は、format()
の%I
指定を使い自動引用符付けされたテーブル名または列名を挿入することです。
EXECUTE format('SELECT count(*) FROM %I ' 'WHERE inserted_by = $1 AND inserted <= $2', tabname) INTO c USING checked_user, checked_date;
(この例は、改行により分割された文字列リテラルが暗黙に連結されるというSQL規則に依存しています。)
他にもパラメータ記号は最適化可能なSQLコマンド(SELECT
、INSERT
、UPDATE
、DELETE
、MERGE
、およびこのうちの1つを含む特定のコマンド)でしか動作しない、という制限があります。
他の種類の文(一般的にユーティリティ文と呼ばれます)では、単なるデータ値であったとしてもテキストの値として埋め込まなければなりません。
最初の例のように、単純な定数コマンドとUSING
パラメータを使ったEXECUTE
は、コマンドを直接PL/pgSQLで書いて、PL/pgSQL変数を自動的に置換したものと機能的に同じです。
重要な差異として、EXECUTE
が現在のパラメータ値に特化した計画を生成し、コマンドを実行する度に計画を再作成することです。
一方、PL/pgSQLはその他に汎用的な計画を作成し、再使用に備えキャッシュします。
最適な計画がパラメータ値に大きく依存する場合、汎用的な計画が選択されないことを確保するために、EXECUTE
の使用は助けになります。
SELECT INTO
はEXECUTE
では現在サポートされません。
代わりに、普通のSELECT
コマンドを実行し、EXECUTE
の一部としてINTO
を記述してください。
PL/pgSQL EXECUTE
文はPostgreSQLサーバでサポートされているEXECUTE
SQL文とは関連がありません。
サーバのEXECUTE
文はPL/pgSQL関数内で使用することはできません(使用する必要もありません)。
例43.1 動的問い合わせの中の値の引用符付け
動的コマンドを使用する時、しばしば単一引用符をエスケープしなければなりません。 関数本体における固定のテキストを引用符付けする推奨方法は、ドル引用符を使用する方法です。 (ドル引用符を用いない旧式のコードを保有している場合は、43.12.1の概要を参照することが、理解しやすいコードへの変換作業の手助けになります)。
動的な値は引用符を含んでいる可能性があるので注意深い取り扱いが必要です。
以下にformat()
を使う例を示します(ここでは関数にドル引用符を用いる方法を使用すると仮定しているので、引用符を二重化する必要はありません)。
EXECUTE format('UPDATE tbl SET %I = $1 ' 'WHERE key = $2', colname) USING newvalue, keyvalue;
クオート関数を直接呼び出すことも可能です。
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = ' || quote_literal(keyvalue);
この例は、quote_ident
とquote_literal
関数(9.4を参照)の使用方法を示しています。
安全のため、列またはテーブル識別子を含む式は動的問い合わせに挿入する前にquote_ident
を介して渡されなくてはなりません。
組み立てられるコマンドの中のリテラル文字列となるはずの値を含む式は、quote_literal
を介して渡されなければなりません。
これらの関数は、すべての特殊文字を適切にエスケープして埋め込んだ、二重引用符または単一引用符で囲まれた入力テキストを返すために、適切な手順を踏みます。
quote_literal
はSTRICT
ラベル付けされているため、NULL引数で呼び出された場合、常にNULLを返します。
上記の例で、newvalue
またはkeyvalue
がNULLの場合、動的問合せ文字列全体がNULLとなり、EXECUTE
からのエラーを導きます。
quote_nullable
関数を使用することで、この問題を回避することができます。
その動作は、NULL引数付きで呼び出された場合に文字列NULL
を返すことを除いてquote_literal
と同一です。
以下に例を示します。
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_nullable(newvalue) || ' WHERE key = ' || quote_nullable(keyvalue);
NULLの可能性のある値を処理するのであれば、通常quote_literal
の代わりにquote_nullable
を使用しなければなりません。
いつものように、問い合わせの中のNULL値は意図しない結果を確実にもたらさないよう配慮をしなければなりません。
例えば次のようなWHERE
句の結果はどうなるのでしょう。
'WHERE key = ' || quote_nullable(keyvalue)
これはkeyvalue
がNULLである限り成功しません。
その理由は、等価演算子=
をNULLオペランドで使用するとその結果は常にNULLとなるからです。
NULLを通常のキーの値と同じように動作させたい場合、上記を、以下のように書き換えなければなりません。
'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
(現時点では、IS NOT DISTINCT FROM
は=
よりもより効率性が少なく扱われますので、必要に迫られた場合以外は行わないようにしてください。
NULLとIS DISTINCT
についての更なる情報は9.2を参照してください。)
ドル引用符は固定のテキストを引用符付けする場合のみ有用であるということに注意してください。 この例を次のように記述するのは非常に悪い考えです。
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = $$' || newvalue || '$$ WHERE key = ' || quote_literal(keyvalue);
なぜなら、newvalue
の内容がたまたま$$
を含む時は、途中で次の処理へ移ってしまうからです。
同様の不測事態は、ドル引用符の他の区切り文字を選んだ時も起こります。
したがって、テキストの内容を把握していない時は、安全にテキストを引用符付けするために、quote_literal
、quote_nullable
、またはquote_ident
関数を適切に使用しなければなりません。
動的なSQL文もformat
関数(9.4.1を参照)を使って安全に作ることができます。例を示します。
EXECUTE format('UPDATE tbl SET %I = %L ' 'WHERE key = %L', colname, newvalue, keyvalue);
%I
はquote_ident
と同等で、%L
はquote_nullable
と同等です。
format
関数はUSING
句と共に使用できます。
EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) USING newvalue, keyvalue;
変数が、無条件にテキストに変換されて%L
で引用符付けされることなく、固有のデータ形式で処理されるため、この形式はより優れています。
動的問い合わせとEXECUTE
の長大な例は例 43.10にあります。
それは新しい関数を定義するためにCREATE FUNCTION
コマンドを組み立て実行するものです。
コマンドの効果を判断するにはいくつか方法があります。
最初の方法は以下のような形式のGET DIAGNOSTICS
を使用する方法です。
GET [ CURRENT ] DIAGNOSTICSvariable
{ = | := }item
[ , ... ];
このコマンドによってシステムステータスインジケータを取り出すことができます。
CURRENT
は無意味な単語です(しかし43.6.8.1のGET STACKED DIAGNOSTICS
も参照してください)。
各item
は、指定されたvariable
(これは受け取るために正しいデータ型でなければなりません)に代入されるステータス値を識別するキーワードです。
現在使用可能なステータス項目は、表 43.1で示されています。
代入記号(:=
)が標準SQLにおける等号(=
)の代わりに使用できます。
以下に例を示します。
GET DIAGNOSTICS integer_var = ROW_COUNT;
表43.1 使用できるステータス項目
名前 | 型 | 説明 |
---|---|---|
ROW_COUNT | bigint | 最後のSQLコマンドにより処理された行数 |
PG_CONTEXT | text | 現在の呼び出しスタックを記述したテキストの行 (43.6.9を参照) |
PG_ROUTINE_OID | oid | 現在の関数のOID |
コマンドの効果を判断する2番目の方法は、FOUND
というboolean
型の特殊な変数を検査することです。
PL/pgSQLの各関数呼び出しで使用される際、FOUND
は最初は偽に設定されています。
以下のように、それぞれの文の種類によって設定が変更されます。
SELECT INTO
文は、行が代入された場合は真、返されなかった場合は偽をFOUND
に設定します。
PERFORM
文は、1つ以上の行が生成(破棄)された場合は真、まったく生成されなかった場合は偽をFOUND
に設定します。
UPDATE
、INSERT
、DELETE
、およびMERGE
文は、少なくとも1行が影響を受けた場合は真、まったく影響を受けなかった場合は偽をFOUND
に設定します。
FETCH
文は、行が返された場合は真、まったく返されなかった場合は偽をFOUND
に設定します。
MOVE
文は、カーソルの移動が成功した場合は真、失敗した場合は偽をFOUND
に設定します。
FOR
文またはFOREACH
文は、1回以上繰り返しが行われた場合は真、行われなかった場合は偽をFOUND
に設定します。
FOUND
はループが終了した際、このように設定されます。
ループ実行中はループ文によるFOUND
の変更はありません。
ただし、ループ本体内の他種類の文を実行することによって、変更されるかもしれません。
RETURN QUERY
とRETURN QUERY EXECUTE
文は、問い合わせが行を1つでも返せば真、行が返されなければ偽をFOUND
に設定します。
他のPL/pgSQL文はFOUND
の状態を変更しません。
特に、EXECUTE
はGET DIAGNOSTICS
の出力を変更しますが、FOUND
を変更しないことに注意してください。
FOUND
はそれぞれのPL/pgSQL関数内部のローカル変数です。
FOUND
に対して行われた全ての変更は、現在の関数にのみ影響します。
何もしないプレースホルダ文が有用になることがあります。
例えば、IF/THEN/ELSE文の一部が空文であることを明示したい時です。
このような目的にはNULL
文を使用します。
NULL;
例えば、次の2つのコードは同等です。
BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN NULL; -- 誤りを無視する END;
BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN -- 誤りを無視する END;
どちらが望ましいと思うかは、好みの問題です。
OracleのPL/SQLでは無記述の文は許されませんので、こうした状況ではNULL
文が必須です。
しかしPL/pgSQLでは無記述の文が許可されています。