23.5. 基本的な文

23.5.1. 代入

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

identifier := expression;

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

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

例

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

23.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 句を許す場所に制限が加えられるかもしれません。

FOUND というboolean 型の特殊な変数があります。これをSELECT INTO の直後に使用して、代入が成功したかどうか(つまり、少なくても1行がSELECTによって返されたかどうか)を確認することができます。以下に例を示します。

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
        -- user entered no homepage, return "http://"

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

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

式、もしくは、結果が不要な問い合わせ(有用な副作用を持つが結果の値は意味がない関数の呼び出しが典型的な目的です)を実行したい場合があります。これを PL/pgSQL で行うには、PERFORM 文を使用して下さい。

PERFORM query;

これは、SELECT query を実行し、その結果を破棄します。PL/pgSQL 変数は通常通り問い合わせ内に代入されます。

例

PERFORM create_mv(''cs_session_page_requests_mv'',''
     SELECT   session_id, page_id, count(*) AS n_hits,
              sum(dwell_time) AS dwell_time, count(dwell_time) AS dwell_count
     FROM     cs_fact_table
     GROUP BY session_id, page_id '');

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

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

EXECUTE query-string;

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

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

動的な問い合わせを行う時、PL/pgSQL では単一引用符をエスケープしなければなりません。作業を補助する、詳細な説明については Section 23.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;  -- Declare a generic record to be used in a 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 ''; 

    -- 
    -- Notice how we scan through the results of a query in a FOR loop
    -- using the FOR <record> construct.
    --

    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'''';''; 
 
    -- This works because we are not substituting any variables
    -- Otherwise it would fail. Look at PERFORM for another way to run functions
    
    EXECUTE a_output; 
END; 
' LANGUAGE 'plpgsql';

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

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

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