24.2. 説明

24.2.3.1. デフォルト値をもつ定数と変数

宣言には以下の構文を使用します。

name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } value ];

CONSTANT をつけて宣言される変数の値は変更することはできません。 NOT NULL を指定した場合には、変数の値に NULL を割り当てるとラン タイムエラーとなります。すべての変数のデフォルト値が SQLの NULL なので、NOT NULL として宣言される すべての変数はデフォルト値を指定しておかなければいけません。

デフォルト値は関数が呼ばれる度に評価されます。 timestamp型の変数に'now'を割り当 てておくと、変数には関数がバイトコードにプリコンパイルされた時で はなくて実際に関数が呼び出された時間がわたります。

例

quantity INTEGER := 32;
url varchar := ''http://mysite.com'';
user_id CONSTANT INTEGER := 10;

24.2.3.2. 関数への変数のわたり方

関数への変数のわたり方は$1, $2 というように識別されます。(最大 16)いくつ かの例を示します。

CREATE FUNCTION sales_tax(REAL) RETURNS REAL AS '
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    return subtotal * 0.06;
END;
' LANGUAGE 'plpgsql';


CREATE FUNCTION instr(VARCHAR,INTEGER) RETURNS INTEGER AS '
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
    -- Some computations here
END;
' LANGUAGE 'plpgsql';

24.2.3.3. 属性

%TYPE と %ROWTYPE 属性を使用することで、 もう一つのデータベース項目と同じデータ型もしくは構成で変数を宣言 することができます。(たとえば、テーブルのフィールド)

24.2.3.4. RENAME

RENAME を使用すると変数、レコード、行の名前を変更することができ ます。これは、トリガプロシージャ内でNEWまたはOLDを別の名前で参照 すべき時に有益です。

構文と使用例です。

RENAME oldname TO newname;

RENAME id TO user_id;
RENAME this_var TO that_var;

24.2.5.1. 代入

変数や行/レコードフィールドへの値の代入は次のように記述されます。

identifier := expression;

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

24.2.5.2. 他の関数の呼び出し

Postgresデータベース内に定義された全て の関数は値を返します。したがって、関数の通常の呼び出し方法は、 SELECT を実行することか(PL/pgSQL内部で SELECT を実行した結果であ る)代入を行うことです。

しかし、関数の結果には興味が無い場合もあります。この場合は PERFORM 文を使用しています。

PERFORM query

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 '');

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

PL/pgSQL関数を内部で動的問い合わせを行いたいということがあるかも しれません。もしくは他の関数を生成する関数をお持ちかもしれません。 PL/pgSQLは、EXECUTE文をこういう場合のために用意しています。

EXECUTE query-string

動的な問い合わせを行うとき、PL/pgSQL ではシングルクォートをエスケー プしなければいけません。詳細な説明として、"オラクルの PL/SQL から の移植"という章で利用できるテーブルを参照すれば労力の節約になりま す。

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

SELECT問い合わせのからの結果は EXECUTE によって捨てられます。そし て、SELECT INTO は EXECUTE 範囲内では現在サポートされません。そし て、結果を動的に作成された SELECT から抜き出す唯一の方法は後で述 べる FOR ... 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; ''; 

    -- 
    -- 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';

24.2.5.4. 返値ステータスの取得

GET DIAGNOSTICS variable = item [ , ... ]

24.2.5.5. 関数からの返り値

RETURN 式

式の結果は自動的に、代入用に記述された関数の戻り値の型にキャスト されます。

24.2.6.1. 制御構造: IF 文

IF文で、特定の条件によって行動を起こすもので す。PL/pgSQLには3つの IF の形式があります。IF-THEN, IF-THEN-ELSE, IF-THEN-ELSE IF. 注: 全ての PL/pgSQL で IF 文は対応 するEND IF文を必要とします。ELSE-IF文では2 つ必要です。1つは最初の IF でもう1つは2番目(ELSE IF)です。

24.2.6.2. 繰り返し制御:LOOP、WHILE、FORとEXIT

LOOP、WHILE、FORとEXIT文で、PL/pgSQLプログラムの実行の流れを制 御することができます。

24.2.7.1. 宣言

RECORD 型の1つの変数は、様々な選択に使うことができます。その中の 行が終わる物が無いときにはランタイムエラーが起こるので、レコード にアクセスする か 1つのレコードフィールドに1つの値を割り当てる ことを試みてください。このように宣言することができます。

name RECORD;

24.2.7.2. 代入

SELECT 結果をすべてレコードや行に代入するには次のようにします。

SELECT  INTO target expressions FROM ...;

行、または変数のリストをターゲットとして使用する場合には、SELECTの結果の 値はターゲットの構造と完全に一致する必要があり、一致しない場合には ランタイムエラーが起こります。FROMキーワードの後にあらゆる有効な修飾や グループ分け、ソートなどを指定することができ、これらはSELECT文に 引き渡すことができます。

一旦レコード、または行がRECORD変数に代入されたら、"."(ドット)表記を使用して そのレコードのフィールドにアクセスすることができます。

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

  full_name := users_rec.first_name || '' '' || users_rec.last_name;

FOUNDという特殊変数があります。これはSELECT INTOが実行されたら 直ちに使用可能で、SELECT INTO文が成功したか否かの確認を行い boolean 型を返します。

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

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;

24.2.7.3. レコード処理の繰り返し

FORループの特別な型を使用することにより、問い合わせの結果を繰り返し取り出し、 それに応じてそのデータを処理することができます。構文は以下の通りです。

[<<label>>]
FOR record | row IN select_clause LOOP
    statements
END LOOP;

create function cs_refresh_mviews () returns integer as '
DECLARE
     mviews RECORD;

     -- Instead, if you did:
     -- mviews  cs_materialized_views%ROWTYPE;
     -- this record would ONLY be usable for the cs_materialized_views table

BEGIN
     PERFORM cs_log(''Refreshing materialized views...'');

     FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP

         -- Now "mviews" has one record from cs_materialized_views

         PERFORM cs_log(''Refreshing materialized view '' || mview.mv_name || ''...'');
         TRUNCATE TABLE mview.mv_name;
         INSERT INTO mview.mv_name || '' '' || mview.mv_query;
     END LOOP;

     PERFORM cs_log(''Done refreshing materialized views.'');
     return 1;
end;
' language 'plpgsql';

レコード処理の繰り返しのもう1つの方法として、FOR-IN EXECUTE文が使用できます。

[<<label>>]
FOR record | row IN EXECUTE text_expression LOOP 
    statements
END LOOP;