37.6. 基本的な文

37.6.1. 代入

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

identifier := expression;

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

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

例

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

37.6.2. SELECT INTO

複数列を持つ(しかし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の全ての機能を使用することができます。

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

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

SELECT INTO文の直後にFOUNDを使用することにより、代入が正しく行なわれたかどうか(つまり、問い合わせによって少なくとも 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;
    full_name varchar;
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;

37.6.3. 結果を返さない式、もしくは問い合わせの実行

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

PERFORM query;

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

以下に例を示します。

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

37.6.4. 動的コマンドの実行

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

EXECUTE command-string;

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

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

動的コマンドを行う時、PL/pgSQLでは単一引用符をエスケープしなければなりません。 項37.2.1の概要を参照することが、作業の手助けになるかもしれません。

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

SELECT問い合わせのからの結果はEXECUTEによって捨てられます。 また、SELECT INTOはEXECUTEでは現在サポートされません。 結果を動的に作成されたSELECTから結果を抜き出す方法は2つあります。 1つは項37.7.4で述べるFOR-IN-EXECUTEを使用することです。 もう1つは項37.8.2で述べるOPEN-FOR-EXECUTEによるカーソルを使用することです。

以下に例を示します。

EXECUTE ''UPDATE tbl SET ''
        || quote_ident(colname)
        || '' = ''
        || 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;''; 
 
    EXECUTE a_output; 
END; 
' LANGUAGE plpgsql;

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

コマンドの効果を判断するには、いくつか方法があります。 最初の方法は、以下のような形式の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は、ローカル変数です。 FOUNDに対して行なわれた全ての変更は、現在のPL/pgSQL関数にのみ影響します。