[11/15開催: PostgreSQL Conference Japan 2019 参加受付中] 
他のバージョンの文書 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

19.5. 基本的な文

本節および次節では、明示的に PL/pgSQL で解釈される、すべての種類の文について説明します。これら種類の文として認められないものは全て、SQL 問い合わせであると仮定され、(その文で使用されるPL/pgSQL変数を全て代入した後で)メインデータベースエンジンに送信され実行されます。従って、例えば、SQL INSERTUPDATEDELETE コマンドは、PL/pgSQL の文とみなすことができます。しかし、ここではこれらについては言及しません。

19.5.1. 代入

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

識別子 := ;

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

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

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

19.5.2. SELECT INTO

複数列を持つ (しかし 1 行のみの) SELECT コマンドの結果は、レコード変数、行型の変数、スカラ変数のリストに代入することができます。これは、以下によって行われます。

SELECT INTO target expressions FROM ...;

ここで、target はレコード変数、行変数、あるいは、単純な変数とレコード/行変数のフィールドをコンマで区切ったリストです。 これは、INTO の対象は新しく作成されるテーブルであるという、PostgreSQL の通常の SELECT INTO の実装とは大きく異なります(PL/pgSQL 関数内部で SELECT の結果からテーブルを作成したい場合は、CREATE TABLE ... AS SELECT 構文を使用して下さい)。

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

INTO 句を除き、SELECT 文は通常の SQL SELECT 問い合わせと同一であり、SELECT の全ての機能を使用することができます。

SELECT 問い合わせが行を返さない場合、NULL 値が対象に代入されます。 SELECT 問い合わせが複数の行を返す場合、最初の行が対象に代入され、残りは破棄されます("最初の行" はORDER BY を使用していない限り完全に明確にならないことに注意して下さい)。

現在、INTO 句はSELECT 問い合わせのほとんど任意の場所に記述することができますが、上で示した通りに SELECT キーワードの直後に記述することを推奨します。将来のバージョンの PL/pgSQL では、INTO 句を記述する場所に制限が加えられるかもしれません。

SELECT INTO 文の直後に FOUND を 使用することにより、代入が正しく行なわれたかどうか (つまり、SELECT 文によって少なくとも 1 行が返されたかどうか) を確認することができます。たとえば、以下のようにします。

SELECT INTO myrec * FROM EMP WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION ''employee % not found'', myname;
END IF;

他の選択肢として、IS NULL (または ISNULL) 条件を使用して、RECORD もしくは ROW 結果が NULL かどうかを検査することができます。他の行が存在し無視されたかどうかを判断する方法は存在しないことに注意して下さい。

DECLARE
    users_rec RECORD;
    full_name varchar;
BEGIN
    SELECT INTO users_rec * FROM users WHERE user_id=3;

    IF users_rec.homepage IS NULL THEN
        -- ユーザがホームページを入力しなかった。 "http://" を返す

        RETURN ''http://'';
    END IF;
END;

19.5.3. 結果を持たらさない式、もしくは問い合わせの実行

結果を必要としない問い合わせや式の評価を行いたいことがあります(直接得られる結果は必要がなくても、副次的に得られる結果に意味を見出す関数の呼び出しが典型的)。これを PL/pgSQL で行うには、PERFORM 文を使用して下さい。

PERFORM query;

これは、SELECT queryを実行し、その結果を破棄します。 PL/pgSQL 変数は、通常通り問い合わせ内で置き換えられます。 また、特殊な変数である FOUND は、問い合わせ結果が 1 行でも生成された場合は true に設定され、生成されない場合は false に設定されます。

Note: INTO 句を持たないSELECT でこの結果を果たすことができると考えるかもしれませんが、現時点では、これを行う方法は PERFORM しかありません。

以下に例を示します。

PERFORM create_mv(''cs_session_page_requests_mv'', my_query);

19.5.4. 動的問い合わせの実行

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

EXECUTE query-string;

ここで、query-string は、実行される query を持つ(text型の)文字列を与える式です。この文字列は SQL エンジンにそのまま渡されます。

この問い合わせ文字列上への PL/pgSQL 変数の代入は行われないことには、特に注意して下さい。 変数の値は、問い合わせ文字列を作成するときに、問い合わせ文字列に埋め込まなければなりません。

動的な問い合わせを行う時、PL/pgSQL では単一引用符をエスケープしなければなりません。作業を補助する、詳細な説明についてはSection 19.11 のテーブルを参照して下さい。

PL/pgSQL における他の全ての問い合わせとは異なり、EXECUTE文によって query を実行する場合は、サーバの生存期間中一度だけ解釈、保存されるわけではありません。代わりに、queryは文が実行される時に準備されます。query-stringは、変数テーブルとフィールドに及ぼす働きを実行するために、プロシージャ内部で動的につくられることができます。

SELECT問い合わせのからの結果は EXECUTE によって捨てられます。 そして、SELECT INTO は EXECUTE では現在サポートされません。そして、結果を動的に作成された SELECT から結果を抜き出す唯一の方法は後で述べる FOR-IN-EXECUTE を使用することです。

以下に例を示します。

EXECUTE ''UPDATE tbl SET ''
        || quote_ident(fieldname)
        || '' = ''
        || quote_literal(newvalue)
        || '' WHERE ...'';

この例は、quote_ident(TEXT) とquote_literal(TEXT)関数の使用方法を示しています。フィールドとテーブル識別子を含んでいる変数は関数quote_ident()を通らなければいけません。動的な問い合わせ文字列のリテラル要素を含んでいる変数はquote_literal()を通らなければいけません。両方とも、適切な処理を行い、入力されたテキストを単一引用符もしくは二重引用符で括り、特殊文字をすべて適切にエスケープしたものを返します。

動的問い合わせと EXECUTE の大きめの例です。

CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS '
DECLARE
    referrer_keys RECORD;  -- FOR で使用される汎用記憶場所を宣言
    a_output varchar(4000);
BEGIN
    a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar) 
                  RETURNS VARCHAR AS '''' 
                     DECLARE 
                         v_host ALIAS FOR $1; 
                         v_domain ALIAS FOR $2; 
                         v_url ALIAS FOR $3;
                     BEGIN ''; 

    -- 
    -- FOR <record> 構文を使用した FOR ループで、問い合わせの結果
    -- をどのようにスキャンしているかに注意して下さい。
    --

    FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
        a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE '''''''''' 
                 || referrer_keys.key_string || '''''''''' THEN RETURN '''''' 
                 || referrer_keys.referrer_type || ''''''; END IF;''; 
    END LOOP; 
  
    a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';''; 
    -- これはどの変数も置き換えていないのでうまくいきます。
    -- さもなければ失敗します。関数を実行する他の方法については PERFORM を参照して下さい。
    
    EXECUTE a_output; 
END;
' LANGUAGE 'plpgsql';

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

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

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

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

      GET DIAGNOSTICS var_integer = ROW_COUNT;

FOUND という boolean 型の特殊な変数があります。 PL/pgSQL の各関数内で使用される際、FOUND は最初は false に設定されています。 以下のように、それぞれの文によって設定が変更されます。

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