本節および次節では、明示的にPL/pgSQLで解釈される、全ての種類の文について説明します。 これらの種類の文として認められないものは全て、SQLコマンドであると仮定され、(その文で使用されるPL/pgSQL変数を全て代入した後で)メインデータベースエンジンに送信され実行されます。 従って、例えば、INSERT、UPDATE、DELETE SQLコマンドは、PL/pgSQL の文とみなすことができます。しかし、ここではこれらについては言及しません。
値を変数、もしくは行/レコードフィールドに代入する場合は以下のように記述します。
identifier := expression;
上述した通り、このような文中にある式は、メインデータベースエンジンに送信されるSELECT SQLコマンドによって評価されます。 式は1つの値を生成しなければなりません。
式の結果データ型が変数のデータ型に一致しない場合、または、変数が(char(20)のように)特定の大きさ/精度を持つ場合、結果の値は PL/pgSQLインタプリタによって、結果の型の出力関数と変数の型の入力関数を使用して暗黙的に変換されます。 これにより、結果値の文字列形式を入力関数で受け付けることができない場合に、入力関数において実行時エラーが発生する可能性があることに注意してください。
例
user_id := 20; tax := subtotal * 0.06;
複数列を持つ(しかし1行のみの)SELECTコマンドの結果は、レコード変数、行型の変数、スカラ変数のリストに代入することができます。 これは、以下によって行われます。
SELECT INTO target select_expressions FROM ...;
ここで、targetはレコード変数、行変数、あるいは、単純な変数とレコード/行変数のフィールドをコンマで区切ったリストです。 select_expressionsとコマンドの残りの部分は通常のSQLと同じです。
これは、INTOの対象は新しく作成されるテーブルであるという、PostgreSQLの通常のSELECT INTOの実装とは大きく異なります。 PL/pgSQL 関数内部でSELECTの結果からテーブルを作成したい場合は、CREATE TABLE ... AS SELECT構文を使用してください。
行または変数リストが対象に使用された場合、選択された値は正確に対象の構造に一致しなければなりません。 さもなければ、実行時エラーが発生します。 レコード変数が対象の場合は、自動的に問い合わせ結果の列の行型に自動的に自身を設定します。
INTO句以外は、このSELECT文は通常のSELECT SQLコマンドと同一であり、SELECTの全ての機能を使用することができます。
INTO 句は SELECT 文のほとんど任意の場所に記述することができます。 習慣的には、上に示したように SELECT の直後または FROM の直前、すなわち select_expressions の直前または直後 に記述されます。
問い合わせが行を返さない場合、NULL値が対象に代入されます。 問い合わせが複数の行を返す場合、最初の行が対象に代入され、残りは破棄されます。 ("最初の行"はORDER BYを使用していない限り完全に明確にならないことに注意してください。)
SELECT INTO 文の後で特殊な FOUND 変数 (項35.6.6を参照) を検査することにより、代入が正しく行なわれたかどうかを、つまり問い合わせによって少なくとも 1 行が返された かどうかを確認できます。
SELECT INTO myrec * FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;
IS NULL条件を使用して、レコードもしくは行結果がNULLかどうかを検査することができます。 しかし、他の行が存在し破棄されたかどうかを判断する方法は存在しないことに注意してください。 以下に、行が全く返されなかった場合を扱う例を示します。
DECLARE
users_rec RECORD;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id=3;
IF users_rec.homepage IS NULL THEN
-- ユーザがhomepageを入力をしなかったので"http://"を返す。
RETURN 'http://';
END IF;
END;
結果を必要としない問い合わせや式の評価を行いたいことがあります(直接得られる結果は必要がなくても、副次的に得られる結果に意味を見出す関数の呼び出しが典型的)。 これを PL/pgSQLで行うには、PERFORM文を使用してください。
PERFORM query;
これは query を実行し、その結果を破棄します。 SQL の SELECT 文と同じ方法で query を記述しますが、最初のキーワード SELECT を PERFORM に置き換えてください。 PL/pgSQL 変数は通常通り問い合わせ内で置き換えられます。 また、特殊な変数である FOUND は問い合わせ結果が 1 行でも生成された場合は真に設定され、生成されない場合は偽に設定されます。。
注意: INTO句を持たないSELECTでこの結果を果たすことができると考えるかもしれませんが、現時点では、これを行う方法はPERFORMしかありません。
以下に例を示します。
PERFORM create_mv('cs_session_page_requests_mv', my_query);
何もしないプレースホルダ文が有用になることがあります。 例えば、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 では、無記述の文が許可されています。
PL/pgSQL関数の内部で、動的コマンド、つまり実行する度に別のテーブルや別のデータ型を使用するコマンドを行いたいということがよくあるでしょう。 PL/pgSQLが通常行うコマンドの計画のキャッシュはこのような状況では動作しません。 この種の問題を扱うために、以下のEXECUTE文が用意されています。
EXECUTE command-string;
ここで、command-stringは、実行されるコマンドを持つ(text型の)文字列を与える式です。 この文字列はSQLエンジンにそのまま渡されます。
このコマンド文字列上へのPL/pgSQL変数の置換は行われないことには、特に注意してください。 変数の値は、コマンド文字列を作成する時に埋め込まなければなりません。
PL/pgSQL における他の全てのコマンドとは異なり、EXECUTE によって実行されるコマンドはセッションの有効期間中一度だけ解釈、保存されるわけではありません。 代わりに、コマンドは文が実行される時に準備されます。 コマンド文字列は、異なるテーブルと列に対する操作を実行できるように、関数内部で動的に作成することができます。
SELECT 問い合わせのからの結果は EXECUTE によって捨てられます。 また、SELECT INTO は EXECUTE では現在サポートされません。 したがって、簡単な EXECUTE コマンドを使用して、動的に作成された SELECT から結果を抜き出す方法はありません。 しかし、その方法は他に 2つあります。 1つは項35.7.4で述べる FOR-IN-EXECUTE ループを使用することです。 もう 1つは項35.8.2で述べる OPEN-FOR-EXECUTE によるカーソルを使用することです。
動的コマンドを行う時、PL/pgSQL では単一引用符をエスケープしなければなりません。 推奨されるのは、関数本体における固定のテキストをドル引用符で囲む方法です。 (ドル引用符を用いない旧式のコードを保有している場合は、項35.2.1の概要を参照することが、理解しやすいコードへの変換作業の手助けになります。)
作成した問い合わせに挿入すべき動的な値は、それ自身の内部に引用符を含む可能性があるため、特別な処理が必要です。 以下に例を示します (ここでは関数にドル引用符を用いる方法を使用すると仮定しているので、引用符を二重化する必要はありません)。
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = '
|| quote_literal(newvalue)
|| ' WHERE key = '
|| quote_literal(keyvalue);
この例は、quote_ident(text)とquote_literal(text)関数の使用方法を示しています。 安全のため、列とテーブルの識別子を含んでいる変数はquote_ident()関数を通らなければいけません。 動的に構築されるコマンド内で、リテラル文字列となるべき値を含んでいる変数はquote_literal()関数を通らなければいけません。 両方とも、適切な処理を行い、入力されたテキストを単一引用符もしくは二重引用符で括り、特殊文字を全て適切にエスケープして埋め込んだものを返します。
ドル引用符の方法は一定のテキストを囲むときだけ有用だということに注意してください。 上例を次にようにするのは、とても悪い考えです。
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = $$'
|| newvalue
|| '$$ WHERE key = '
|| quote_literal(keyvalue);なぜなら、newvalue の内容がたまたま $$ を含むときは、途中で次の処理へ移ってしまうからです。 同様の不測事態は、ドル引用符の他の区切り文字を選んだときも起こります。 したがって、テキストの内容が既知でないときは、安全のために quote_literal 関数を使用しなければなりません。
動的問い合わせと EXECUTE の長大な例は例35-5で見ることができます。 それは新しい関数を定義するために CREATE FUNCTION コマンドを用いて実行するものです。
コマンドの効果を判断するには、いくつか方法があります。 最初の方法は、以下のような形式のGET DIAGNOSTICSを使用する方法です。
GET DIAGNOSTICS variable = item [ , ... ] ;このコマンドによって、システムステータスインジケータを取り出すことができます。 各itemは、指定された変数に割り当てられた状態値を識別するためのキーワードです(これは受けとるために正しいデータ型でなければなりません)。 現在使用可能なステータス項目は、ROW_COUNT、RESULT_OIDの2つです。 ROW_COUNTは、最後にSQLエンジンに送信されたSQLコマンドによって処理された行数を示します。 RESULT_OIDは、最も最近のSQLコマンドによって挿入された最後の行のOIDです。 RESULT_OIDはINSERTコマンドの後でのみ有意であることに注意してください。
以下に例を示します。
GET DIAGNOSTICS integer_var = ROW_COUNT;
2番目のコマンドの効果を判断する方法は、FOUNDというboolean型の特殊な変数をチェックすることです。 PL/pgSQLの各関数内で使用される際、FOUNDは最初は偽に設定されています。 以下のように、それぞれの文の種類によって設定が変更されます。
SELECT INTO文は、行が返された場合は真、返されなかった場合は偽をFOUNDに設定します。
PERFORM文は、行が生成(破棄)された場合は真、生成されなかった場合は偽をFOUNDに設定します。
UPDATE、INSERT、およびDELETE文は、少なくとも1行が影響を受けた場合は真、全く影響を受けなかった場合は偽をFOUNDに設定します。
FETCH文は、行が返された場合は真、全く返されなかった場合は偽をFOUNDに設定します。
FOR 文は 1 回以上繰り返しが行なわれた場合は真、行なわれなかった場合は偽を FOUND に設定します。 これは 3 種の FOR 文全て(整数 FOR ループ、レコードセッ トFOR ループ、動的レコードセット FOR ループ)に当てはまります。 FOUND は FOR ループが終了した際、このように設定されます。 つまり、実行ループ内では FOR 文によって FOUND は変更されません。 ただし、ループ本体内の他種類の文を実行することによって、変更されるかもしれません。
FOUND は各々の PL/pgSQL 関数内部のローカル変数です。 故に、FOUND に対して行なわれた全ての変更は、現在の関数にのみ影響します。