他のバージョンの文書 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

37.6. 基本的な文

本節および次節では、明示的にPL/pgSQLで解釈される、全ての種類の文について説明します。 これらの種類の文として認められないものは全て、SQLコマンドであると仮定され、項37.6.2および項37.6.3において記述したように、メインデータベースエンジンに送信され実行されます。

37.6.1. 代入

値をPL/pgSQL変数、もしくは行/レコードフィールドに代入する場合は以下のように記述します。

identifier := expression;

上述した通り、このような文中にある式は、メインデータベースエンジンに送信されるSELECT SQLコマンドによって評価されます。 式は1つの値を生成しなければなりません。

式の結果データ型が変数のデータ型に一致しない場合、または、変数が(char(20)のように)特定の大きさ/精度を持つ場合、結果の値は PL/pgSQLインタプリタによって、結果の型の出力関数と変数の型の入力関数を使用して暗黙的に変換されます。 これにより、結果値の文字列形式を入力関数で受け付けることができない場合に、入力関数において実行時エラーが発生する可能性があることに注意してください。

例:

user_id := 20;
tax := subtotal * 0.06;

37.6.2. 結果を伴わない問い合わせの実行

例えば、RETURNING句のないINSERTのように、行を返さない SQL の問い合わせにおいて、単に記述することによってPL/pgSQL関数の内部で問い合わせを実行できます。

問い合わせテキストに現れる全てのPL/pgSQL変数名も、パラメータのシンボルに置き換えられます。 その後、実行時のパラメータ値として、その時点の変数値が提供されます。 こうして同じ文体の問い合わせを、異なった関数呼び出しにおいて、異なったものとすることができます。

注意: この 2段階の処理により、PL/pgSQLの問い合わせの計画を 1度だけ作成し、その後の実行では計画の再利用ができます。以下に例を示します。

DECLARE
    key TEXT;
    delta INTEGER;
BEGIN
    ...
    UPDATE mytab SET val = val + delta WHERE id = key;

問い合わせのテキストは、SQL 主エンジンから下のように見えます。

    UPDATE mytab SET val = val + $1 WHERE id = $2;

通常このように考えることはありませんが、構文エラーメッセージの解析力が必要なときは、この解釈が有用となります。

注意

PL/pgSQLは、関数において宣言した変数と一致する全ての識別子への置換を行います。 したがって、関数内の問い合わせで参照しなければならないテーブル名と列名と同じ名前を変数に使用するのは、不適切な考えです。 問い合わせの内部で限定的な名前を使用することにより、このような不適切を回避できます。 fooまたはbarが宣言した変数名である場合においても、PL/pgSQLは限定的な名前foo.barにおける置換は行いません。

式または問い合わせSELECTを評価して結果を破棄することが、役に立つ場合があります。 例えば、関数の呼び出しにおいて、副次的な成果を取得できるが、結果は無用である場合です。 このようなときPL/pgSQLでは、PERFORM文を使用してください。

PERFORM query;

これはqueryを実行し、その結果を破棄します。 SQLのSELECT文と同じ方法でqueryを記述しますが、最初のキーワードSELECTPERFORMに置き換えてください。 PL/pgSQL変数は通常通り問い合わせ内で置き換えられます。 また、特殊な変数であるFOUNDは問い合わせ結果が1行でも生成された場合は真に設定され、生成されない場合は偽に設定されます。

注意: 直接SELECTを記述すれば、この結果を得ることができると考えるかもしれませんが、現時点でこれを行う方法はPERFORMしかありません。 SELECTのように行を返すSQLコマンドは、エラーとして拒絶されます。 なお、INTO句を有するときは例外であり、次節で説明します。

以下に例を示します。

PERFORM create_mv('cs_session_page_requests_mv', my_query);

37.6.3. 1行の結果を返す問い合わせの実行

1行を返す(多分、複数列の)SQL コマンドの結果は、レコード変数、行型の変数、スカラ変数のリストに代入することができます。 これは、基本的なSQL コマンドを記述して、それにINTO句を追加することによって行われます。 以下に例を示します。

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;

ここで、targetはレコード変数、行変数、あるいは、単純な変数とレコード/行変数のフィールドをカンマで区切ったリストです。 PL/pgSQL変数は置換されて、通常の問い合わせの残りと同じになります。 このように作動するのは、RETURNINGを伴ったINSERT/UPDATE/DELETESELECTおよび行セットの結果を返すユーティリティコマンド(例えば、EXPLAIN)です。 INTO句以外では、SQL コマンドはPL/pgSQLの外部に記述したものと同じです。

ティップ: 通常のPostgreSQLSELECT INTO文では、INTOの対象は新しく作成されるテーブルです。 しかし、INTOを伴ったSELECTでは、この解釈が通常と大きく異なることに注意してください。 PL/pgSQL 関数内部でSELECTの結果からテーブルを作成したい場合は、CREATE TABLE ... AS SELECT構文を使用してください。

行または変数リストが対象に使用された場合、列数とデータ型において問い合わせの結果と対象の構造が正確に一致しなければなりません。 さもないと、実行時エラーが発生します。 レコード変数が対象の場合は、問い合わせ結果の列の行型に自分自身を自動的に設定します。

INTO句以外は、このSELECT文は通常のSELECT SQLコマンドと同一であり、SELECTの全ての機能を使用することができます。

INTO句はSQLコマンドのほとんど任意の場所に記述することができます。 習慣的には、SELECT文においてはselect_expressionsの直前または直後に、他のコマンドにおいては文の終わりに記述されます。 将来PL/pgSQLのパーサが厳格になる場合に備えて、この習慣に従うことを推奨します。

STRICTが指定されない場合、targetは問い合わせが返す最初の行となり、行を返さないときはNULLとなります。 ("t最初の行"とはORDER BYを使用しないと定義できないことに注意してください。) 2行目以降の行の結果は、全て破棄されます。 特殊なFOUND変数を調べて、どの行が返されたか知ることができます。 (項37.6.6を参照してください) 以下に例を示します。

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変数を常に真に設定してください。

STRICTが指定されない場合でも、RETURNINGを伴ったINSERT/UPDATE/DELETEが2行以上を返したときは、エラーとなります。 なぜなら、どの1行を返すか決定するORDER BYのようなオプションが存在しないからです。

注意: STRICTオプションは、OracleのPL/SQLのSELECT INTOおよび関連した文に対応します。

SQLの問い合わせが返す複数行の結果を処理したい場合は、項37.7.4を参照してください。

37.6.4. まったく何もしない

何もしないプレースホルダ文が有用になることがあります。 例えば、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では、無記述の文が許可されています。

37.6.5. 動的コマンドの実行

PL/pgSQL関数の内部で、動的コマンド、つまり実行する度に別のテーブルや別のデータ型を使用するコマンドがほしいということがよくあるでしょう。 PL/pgSQLが通常行うコマンドの計画のキャッシュはこのような状況では動作しません。 この種の問題を扱うために、以下のEXECUTE文が用意されています。

EXECUTE command-string [ INTO [STRICT] target ];

ここで、command-stringは実行されるコマンドを含む(text型の)文字列に従った評価式で、 targetはレコード変数、行変数、あるいは、単純な変数とレコード/行変数のフィールドをカンマで区切ったリストです。

PL/pgSQL変数が、この演算用のコマンド文字列へ置換されないことに、特に注意してください。 変数の値は、コマンド文字列を作成する時に埋め込まなければなりません。

PL/pgSQLにおける他の全てのコマンドとは異なり、EXECUTEによって実行されるコマンドはセッションの有効期間中一度だけ解釈、保存されるわけではありません。 代わりに、コマンドは文が実行される時に準備されます。 コマンド文字列は、異なるテーブルと列に対する操作を実行できるように、関数内部で動的に作成することができます。

INTO句は、行を返すSQLコマンドの結果を代入するべき場所を指定します。 行または変数リストが用いられるとき、それは問い合わせの結果の構造と正確に一致しなければなりません。 (レコード変数が使用されるとき、それは自分自身を結果の構造と自動的に一致させます)。 複数の行が返されたとき、最初の行だけがINTO変数に代入されます。 1行も返されないとき、NULL がINTO変数に代入されます。 INTO句が指定されないとき、問い合わせの結果は捨てられます。

STRICTオプションが指定されたとき、問い合わせの結果が正確に1行の場合を除き、エラーとなります。

SELECT INTOEXECUTEでは現在サポートされません。

動的コマンドを実行する時、PL/pgSQLでは単一引用符をエスケープしなければなりません。 推奨されるのは、関数本体における固定のテキストをドル引用符で囲む方法です (ドル引用符を用いない旧式のコードを保有している場合は、項37.2.1の概要を参照することが、理解しやすいコードへの変換作業の手助けになります)。

作成した問い合わせに挿入すべき動的な値は、それ自身の内部に引用符を含む可能性があるため、特別な処理が必要です。 以下に例を示します(ここでは関数にドル引用符を用いる方法を使用すると仮定しているので、引用符を二重化する必要はありません)。

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE key = '
        || quote_literal(keyvalue);

この例は、quote_identquote_literal関数の使用方法を示しています。 安全のため、列とテーブルの識別子を含んでいる式はquote_ident関数を通さなければなりません。 動的に構築されるコマンド内で、リテラル文字列となるべき値を含んでいる式はquote_literal関数を通さなければなりません。 両方とも、適切な処理を行い、入力されたテキストを単一引用符もしくは二重引用符で括り、特殊文字を全て適切にエスケープして埋め込んだものを返します。

ドル引用符の用法は一定のテキストを囲む時だけ有用だということに注意してください。 上の例を次のようにするのは、とても悪い考えです。

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = $$'
        || newvalue
        || '$$ WHERE key = '
        || quote_literal(keyvalue);

なぜなら、newvalueの内容がたまたま$$を含む時は、途中で次の処理へ移ってしまうからです。 同様の不測事態は、ドル引用符の他の区切り文字を選んだ時も起こります。 したがって、テキストの内容が既知でない時は、安全のためにquote_literal関数を使用しなければなりません

動的問い合わせとEXECUTEの長大な例は例37-6で見ることができます。 それは新しい関数を定義するためにCREATE FUNCTIONコマンドを用いて実行するものです。

37.6.6. 結果ステータスの取得

コマンドの効果を判断するには、いくつか方法があります。 最初の方法は、以下のような形式のGET DIAGNOSTICSを使用する方法です。

GET DIAGNOSTICS variable = item [ , ... ];

このコマンドによって、システムステータスインジケータを取り出すことができます。 各itemは、指定された変数に割り当てられた状態値を識別するためのキーワードです(これは受け取るために正しいデータ型でなければなりません)。 現在使用可能なステータス項目は、ROW_COUNTRESULT_OIDの2つです。 ROW_COUNTは、最後にSQLエンジンに送信されたSQLコマンドによって処理された行数を示します。 RESULT_OIDは、最も最近のSQLコマンドによって挿入された最後の行のOIDです。 RESULT_OIDはOID を保有するテーブルへのINSERTコマンドの後でのみ有意であることに注意してください。

以下に例を示します。

GET DIAGNOSTICS integer_var = ROW_COUNT;

2番目のコマンドの効果を判断する方法は、FOUNDというboolean型の特殊な変数をチェックすることです。 PL/pgSQLの各関数内で使用される際、FOUNDは最初は偽に設定されています。 以下のように、それぞれの文の種類によって設定が変更されます。

FOUNDは各々のPL/pgSQL関数内部のローカル変数です。 FOUNDに対して行われた全ての変更は、現在の関数にのみ影響します。