★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 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

43.5. 基本的な文 #

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

43.5.1. 代入 #

値を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;

43.5.2. SQLコマンドの実行 #

一般に、行を返さないSQLコマンドはPL/pgSQL関数内にそのコマンドを書くだけで実行されます。 例えば、テーブルを作成してデータを入れるには以下のように書けます。

CREATE TABLE mytable (id int primary key, data text);
INSERT INTO mytable VALUES (1,'one'), (2,'two');

コマンドが行を返すのであれば(例えばSELECTRETURNINGを伴うINSERT/UPDATE/DELETEなど)、処理する方法が2つあります。 コマンドが多くても1行を返す場合、もしくは出力の最初の行だけに関心がある場合には、43.5.3に書かれているように、出力を取得するためのINTO句を追加する以外は通常通りコマンドを書いてください。 出力行をすべて処理するためには、43.6.6に書かれているように、FORループに対するデータソースとしてコマンドを書いてください。

通常、静的に定義されたSQLコマンドを実行するだけでは十分ではありません。 典型的には、可変のデータ値を使ったり、さらには異なる時には異なるテーブル名を使うなどより基本的な方法で変化したりするコマンドを使いたいでしょう。 今回も、状況に応じて2つの方法があります。

PL/pgSQL変数値は、最適化可能なSQLコマンドに自動的に挿入できます。最適化可能なSQLコマンドとは、SELECTINSERTUPDATEDELETEMERGEEXPLAINCREATE 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を記述しますが、最初のキーワードSELECTPERFORMに置き換えてください。 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);

43.5.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変数によりコマンドの残り(すなわち、INTO句以外のすべて)が置換され、同じように計画がキャッシュされます。 このように作動するのは、SELECTRETURNINGを伴ったINSERT/UPDATE/DELETEとおよびEXPLAINのような行セットの結果を返す特定のユーティリティコマンドです。 INTO句以外では、SQLコマンドはPL/pgSQLの外部に記述したものと同じです。

ヒント

通常のPostgreSQLSELECT 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/pgSQLSTRICTが指定されない場合でも、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および関連した文に対応します。

43.5.4. 動的コマンドの実行 #

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

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

ここで、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コマンド(SELECTINSERTUPDATEDELETEMERGE、およびこのうちの1つを含む特定のコマンド)でしか動作しない、という制限があります。 他の種類の文(一般的にユーティリティ文と呼ばれます)では、単なるデータ値であったとしてもテキストの値として埋め込まなければなりません。

最初の例のように、単純な定数コマンドとUSINGパラメータを使ったEXECUTEは、コマンドを直接PL/pgSQLで書いて、PL/pgSQL変数を自動的に置換したものと機能的に同じです。 重要な差異として、EXECUTEが現在のパラメータ値に特化した計画を生成し、コマンドを実行する度に計画を再作成することです。 一方、PL/pgSQLはその他に汎用的な計画を作成し、再使用に備えキャッシュします。 最適な計画がパラメータ値に大きく依存する場合、汎用的な計画が選択されないことを確保するために、EXECUTEの使用は助けになります。

SELECT INTOEXECUTEでは現在サポートされません。 代わりに、普通の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_identquote_literal関数(9.4を参照)の使用方法を示しています。 安全のため、列またはテーブル識別子を含む式は動的問い合わせに挿入する前にquote_identを介して渡されなくてはなりません。 組み立てられるコマンドの中のリテラル文字列となるはずの値を含む式は、quote_literalを介して渡されなければなりません。 これらの関数は、すべての特殊文字を適切にエスケープして埋め込んだ、二重引用符または単一引用符で囲まれた入力テキストを返すために、適切な手順を踏みます。

quote_literalSTRICTラベル付けされているため、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_literalquote_nullable、またはquote_ident関数を適切に使用しなければなりません

動的なSQL文もformat関数(9.4.1を参照)を使って安全に作ることができます。例を示します。

EXECUTE format('UPDATE tbl SET %I = %L '
   'WHERE key = %L', colname, newvalue, keyvalue);

%Iquote_identと同等で、%Lquote_nullableと同等です。 format関数はUSING句と共に使用できます。

EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
   USING newvalue, keyvalue;

変数が、無条件にテキストに変換されて%Lで引用符付けされることなく、固有のデータ形式で処理されるため、この形式はより優れています。


動的問い合わせとEXECUTEの長大な例は例 43.10にあります。 それは新しい関数を定義するためにCREATE FUNCTIONコマンドを組み立て実行するものです。

43.5.5. 結果ステータスの取得 #

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

GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];

このコマンドによってシステムステータスインジケータを取り出すことができます。 CURRENTは無意味な単語です(しかし43.6.8.1GET STACKED DIAGNOSTICSも参照してください)。 各itemは、指定されたvariable(これは受け取るために正しいデータ型でなければなりません)に代入されるステータス値を識別するキーワードです。 現在使用可能なステータス項目は、表 43.1で示されています。 代入記号(:=)が標準SQLにおける等号(=)の代わりに使用できます。 以下に例を示します。

GET DIAGNOSTICS integer_var = ROW_COUNT;

表43.1 使用できるステータス項目

名前説明
ROW_COUNTbigint最後のSQLコマンドにより処理された行数
PG_CONTEXTtext現在の呼び出しスタックを記述したテキストの行 (43.6.9を参照)
PG_ROUTINE_OIDoid現在の関数のOID

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

  • SELECT INTO文は、行が代入された場合は真、返されなかった場合は偽をFOUNDに設定します。

  • PERFORM文は、1つ以上の行が生成(破棄)された場合は真、まったく生成されなかった場合は偽をFOUNDに設定します。

  • UPDATEINSERTDELETE、およびMERGE文は、少なくとも1行が影響を受けた場合は真、まったく影響を受けなかった場合は偽をFOUNDに設定します。

  • FETCH文は、行が返された場合は真、まったく返されなかった場合は偽をFOUNDに設定します。

  • MOVE文は、カーソルの移動が成功した場合は真、失敗した場合は偽をFOUNDに設定します。

  • FOR文またはFOREACH文は、1回以上繰り返しが行われた場合は真、行われなかった場合は偽をFOUNDに設定します。 FOUNDはループが終了した際、このように設定されます。 ループ実行中はループ文によるFOUNDの変更はありません。 ただし、ループ本体内の他種類の文を実行することによって、変更されるかもしれません。

  • RETURN QUERYRETURN QUERY EXECUTE文は、問い合わせが行を1つでも返せば真、行が返されなければ偽をFOUNDに設定します。

他のPL/pgSQL文はFOUNDの状態を変更しません。 特に、EXECUTEGET DIAGNOSTICSの出力を変更しますが、FOUNDを変更しないことに注意してください。

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

43.5.6. まったく何もしない #

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