38.6. 制御構造

制御構造はおそらくPL/pgSQLの最も有用(かつ重要)な部分です。 PL/pgSQLの制御構造を使用して、PostgreSQLのデータを非常に柔軟、強力に操作することができます

38.6.1. 関数からの復帰

関数からデータを返すために使用できるコマンドが2つあります。 RETURNおよびRETURN NEXTです。

38.6.1.1. RETURN

RETURN expression;

式を後に付けたRETURNは関数を終了し、expressionの値を呼び出し元に返します。 この形式は集合を返さないPL/pgSQL関数で使用されます。

スカラ型を返す場合は任意の式を使用することができます。 この式の結果は、代入のところで説明したように、自動的に関数の戻り値の型にキャストされます。 複合(行)値を返すためには、expressionとしてレコードもしくは行変数を記述しなければなりません。

出力パラメータを持った関数を宣言した時は、式の無いRETURNを記述してください。 その時点における出力パラメータの値が返されます。

voidを返すように関数を宣言した場合でも、 関数を直ちに抜け出すためにRETURNを使用できますが、 RETURNの後に式を記述しないでください。

関数の戻り値は未定義とさせたままにすることはできません。 制御が、RETURN文が見つからない状態で関数の最上位のブロックの終わりまで達した時、実行時エラーが発生します。 しかし、この制限は出力パラメータを持った関数及びvoidを返す関数には当てはまりません。 このような場合は最上位のブロックが終わった時、 RETURN文が自動的に実行されます。

38.6.1.2. RETURN NEXTおよびRETURN QUERY

RETURN NEXT expression;
RETURN QUERY query;

PL/pgSQL関数がSETOF sometypeを返すように宣言した場合、後続の処理が多少違います。 この場合、戻り値の個々の項目は、RETURN NEXTコマンドまたはRETURN QUERYコマンドで指定されます。 そして、引数のない最後のRETURNコマンドにより、関数が実行を終了したことが示されます。 RETURN NEXTは、スカラ型および複合型の両方で使用することができます。 複合型の場合、結果の"テーブル"全体が返されます。 RETURN QUERYは、問い合わせを実行した結果を関数の結果集合に追加します。 RETURN NEXTRETURN QUERYは、単一の集合を返す関数の中で自由に混合できます。 この場合、連結されたものが結果となります。

実際には、RETURN NEXTおよびRETURN QUERYは関数から戻りません。 単に関数の結果集合に行を追加しているだけです。 そして、その実行はPL/pgSQL関数内の次の文に継続します。 RETURN NEXTまたはRETURN QUERYコマンドが連続して実行されると、結果集合が作成されます。 最後の、引数を持ってはならないRETURNにより、関数の終了を制御します。 (または制御を関数の最後に移すことができます)。

出力パラメータを持った関数を宣言した時は、式の無いRETURN NEXTだけを記述してください。 実行の度に、その時点における出力パラメータの値が、関数からの戻り値のために結果の行として保存されます。 出力パラメータを持った集合を返す関数を作成するためには、出力パラメータが複数の時はSETOF recordを返すように関数を宣言し、単一のsometype型の出力パラメータの時はSETOF sometypeを返すように関数を宣言しなければならないことに注意してください。

RETURN NEXTを使用する関数の例を、以下に示します。

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN SELECT * FROM foo
    WHERE fooid > 0
    LOOP
        -- can do some processing here
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM getallfoo();

RETURN NEXTまたはRETURN QUERYを使用する関数は、FROM句でテーブルのソースとして呼び出されなければないことに注意してください。

注意: 上記のように、RETURN NEXTおよびRETURN QUERYの現在の実装では、関数から返される前に結果集合全体を保管します。 これにより、PL/pgSQL関数が非常に大量の結果集合を返した場合、性能が低下する可能性があります。 メモリの消耗を避けるため、データはディスクに書き込まれます。 しかし、関数自体は結果集合全体が生成されるまでは返りません。 将来のPL/pgSQLのバージョンでは、この制限を受けずに集合を返す関数をユーザが定義できるようになるかもしれません。 現在、ディスクに書き込まれるデータの開始点はwork_mem設定変数によって制御されています。 大量の結果集合を保管するのに十分なメモリがある場合、管理者はこのパラメータの値を大きくすることを考慮すべきです。

38.6.2. 条件分岐

IF文はある条件に基づいてコマンドを実行させます。 PL/pgSQLには、以下のような5つのIFの形式があります。

38.6.2.1. IF-THEN

IF boolean-expression THEN
    statements
END IF;

IF-THEN文は、最も単純なIFの形式です。THENEND IFの間の文が条件が真の場合に実行されます。 さもなければそれらは飛ばされます。

例:

IF v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

38.6.2.2. IF-THEN-ELSE

IF boolean-expression THEN
    statements
ELSE
    statements
END IF;

IF-THEN-ELSE文はIF-THENに加え、条件評価が偽の場合に実行すべき文の集合を指定させることができます。

例:

IF parentid IS NULL OR parentid = ''
THEN
    RETURN fullname;
ELSE
    RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;

IF v_count > 0 THEN 
    INSERT INTO users_count (count) VALUES (v_count);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;

38.6.2.3. IF-THEN-ELSE IF

以下の例のようにIF文は入れ子にすることができます。

IF demo_row.sex = 'm' THEN
    pretty_sex := 'man';
ELSE
    IF demo_row.sex = 'f' THEN
        pretty_sex := 'woman';
    END IF;
END IF;

この形式を使用する場合、実際にIF文を外側のIF文のELSEの部分の内側に入れ子にしています。 したがって、入れ子にしたIFごとに1つのEND IF文が、その親となるIF-ELSEに1つのEND IF文が必要です。 これにより正常に動作できますが、検査すべき候補が多くある場合は冗長になります。 その結果、次の形式です。

38.6.2.4. IF-THEN-ELSIF-ELSE

IF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
    ...]]
[ ELSE
    statements ]
END IF;

IF-THEN-ELSIF-ELSEは、ある文に多くの代替手段がある場合のチェックに、より便利な方法を提供します。 機能的には、IF-THEN-ELSE-IF-THENコマンドを入れ子にしたものと同じですが、必要なEND IFは1つだけです。

以下に例を示します。

IF number = 0 THEN
    result := 'zero';
ELSIF number > 0 THEN 
    result := 'positive';
ELSIF number < 0 THEN
    result := 'negative';
ELSE
    -- ふうむ、残る唯一の可能性はその値がNULLであることだ
    result := 'NULL';
END IF;

38.6.2.5. IF-THEN-ELSEIF-ELSE

ELSEIFELSIFの別名です。

38.6.3. 単純なループ

LOOPEXITCONTINUEWHILEFOR文を使用して、PL/pgSQL関数で、一連のコマンド群を繰り返すことができます。

38.6.3.1. LOOP

[ <<label>> ]
LOOP
    statements
END LOOP [ label ];

LOOPは、EXIT文またはRETURN文によって終了されるまで無限に繰り返される、条件なしのループを定義します。 オプションのlabelは、入れ子状ループ内のEXIT及びCONTINUE文で、どのレベルの入れ子に適用するかを指定するために使用されます。

38.6.3.2. EXIT

EXIT [ label ] [ WHEN boolean-expression ];

labelが指定されない場合、最も内側のループを終わらせ、END LOOPの次の文がその後に実行されます。 label が指定された場合、それは現在のループ、もしくは入れ子になったループやブロックの外側のレベルのラベルである必要があります。 その後、指名されたループまたはブロックを終わらせ、そのループまたはブロックの対応するENDの次の文に制御を移します。

WHENが指定された場合、boolean-expressionが真の場合のみループの終了が起こります。 さもなければ、EXITの後の行に制御が移ります。

EXITは、あらゆるループと共に使用でき、条件なしのループに限定されません。 BEGINブロックと共に使用した時、EXITによりブロックの次の文に制御が移ります。

例:

LOOP
    -- 何らかの演算
    IF count > 0 THEN
        EXIT;  -- ループを抜け出す
    END IF;
END LOOP;

LOOP
    -- 何らかの演算
    EXIT WHEN count > 0;  -- 上例と同じ結果
END LOOP;

BEGIN
    -- 何らかの演算
    IF stocks > 100000 THEN
        EXIT;  -- これによりBEGINブロックを抜け出す
    END IF;
END;

38.6.3.3. CONTINUE

CONTINUE [ label ] [ WHEN boolean-expression ];

labelが無い場合、すぐ外側のループの次の繰り返しが開始されます。 すなわち、ループ本体の残りの文は飛ばされて、他のループの繰り返し が必要か決めるため、制御がループ制御式(もし存在すれば)に戻ります。 labelが存在する場合、実行を継続するループのラベルを指定します。

WHENが指定された場合、boolean-expressionが真の場合のみループにおける次の繰り返しが始まります。 さもなければ、CONTINUEの後の行に制御が移ります。

CONTINUEは全ての種類のループで使用可能です。 条件なしのループに限定されません。

LOOP
    -- 何らかの演算
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- 50から100を数える、何らかの演算
END LOOP;

38.6.3.4. WHILE

[ <<label>> ]
WHILE boolean-expression LOOP
    statements
END LOOP [ label ];

WHILE文はboolean-expressionの評価が真である間、文の並びを繰り返します。 条件式は、ループ本体に入る前にのみチェックされます。

以下に例を示します。

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- ここで演算をいくつか行います。
END LOOP;

WHILE NOT done LOOP
    -- ここで演算をいくつか行います。
END LOOP;

38.6.3.5. FOR(整数FORループ)

[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
    statements
END LOOP [ label ];

この形式のFORは整数値の範囲を繰り返すループを生成します。 name変数はinteger型として自動的に定義され、ループ内部のみで存在します (ループ外部で定義しても、ループ内部では全て無視されます)。 範囲の下限、上限として与えられる2つの式はループに入った時に一度だけ評価されます。 BY句を指定しない時の繰り返し刻みは 1ですが、BY句を用いて指定でき、ループに入った時に一度だけ評価されます。 REVERSEが指定された場合、繰り返し刻みの値は繰り返しごとに減算され、加算はされません。

整数FORループの例を以下に示します。

FOR i IN 1..10 LOOP
    -- i はループ内で 1、2、3、4、5、6、7、8、9、10 の値を取ります。
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- i はループ内で 10、9、8、7、6、5、4、3、2、1 の値を取ります。
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- i はループ内で 10、8、6、4、2 の値を取ります。
END LOOP;

下限が上限よりも大きい(REVERSEの場合はより小さい)場合、ループ本体はまったく実行されません。 エラーは発生しません。

labelFORループに付加することにより、labelを用いて修飾した名前の整数ループ変数を参照できます。

38.6.4. 問い合わせ結果による繰り返し

別の種類のFORループを使用して、問い合わせの結果を繰り返し、そのデータを扱うことができます。 以下に構文を示します。

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

targetは、レコード変数、行変数またはカンマで区切ったスカラ変数のリストです。 targetには順次、queryの結果の全ての行が代入され、各行に対してループ本体が実行されます。 以下に例を示します。

CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    PERFORM cs_log('Refreshing materialized views...');

    FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
        -- ここで"mviews"はcs_materialized_viewsの1つのレコードを持ちます
        PERFORM cs_log('Refreshing materialized view ' || quote_ident(mviews.mv_name) || ' ...');
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
        EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || mviews.mv_query;
    END LOOP;

    PERFORM cs_log('Done refreshing materialized views.');
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

このループがEXIT文で終了した場合、最後に割り当てられた行の値はループを抜けた後でもアクセスすることができます。

この型のFOR文のqueryとして、行を返す全てのSQLコマンドを使用できます。 通常はSELECTですが、RETURNING句を伴ったINSERTUPDATEまたはDELETEも使用できます。 EXPLAINなどのユーティリティコマンドも作動します。

PL/pgSQL変数は問い合わせテキストに置き換えられます。 問い合わせ計画は、項38.10.1および項38.10.2で述べたように、再利用のためにキャッシュされます。

FOR-IN-EXECUTE 文は行を繰り返すもう1つの方法です。

[ <<label>> ]
FOR target IN EXECUTE text_expression LOOP 
    statements
END LOOP [ label ];

この方法は、問い合わせのソースが文字列式で指定される点を除き、前の形式と似ています。 この式はFORループの各エントリで評価され、再計画が行われます。 これにより、プログラマは、通常のEXECUTE文と同じように事前に計画された問い合わせによる高速性と、動的な問い合わせの持つ柔軟性を選択することができます。

38.6.5. エラーの捕捉

デフォルトでは、PL/pgSQL関数の内部で発生したエラーは関数の実行を中止し、実際に周辺のトランザクションをアボートします。 BEGINブロックおよびEXCEPTION句を使用すれば、エラーを捕捉してその状態から回復できます。 その構文は通常のBEGINブロックの構文を拡張したものです。

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;

エラーが発生しない時、この形式のブロックは単に全てのstatementsを実行し、ENDの次の文に制御が移ります。 しかし、statementsの内部でエラーが発生すると、それ以後のstatementsにおける処理は中断され、EXCEPTIONに示した一覧に制御が移ります。 そして一覧の中から、発生したエラーと合致する最初のconditionを探します。 合致するものがあれば、対応するhandler_statementsを実行し、ENDの次の文に制御が移ります。 合致するものがなければ、EXCEPTION句が存在しないのと同じで、エラーは外側に波及します。 EXCEPTIONを含んだ外側のブロックはエラーを捕捉できますが、失敗すると関数の処理は中断されます。

全ての状態名は付録Aに示すことができます。 分類名はそこに分類される全てのエラーに合致します。 OTHERSという特別の状態名はQUERY_CANCELED以外の全てのエラーに合致します (QUERY_CANCELEDを名前で捕捉することは可能ですが、賢明ではありません)。 状態名は、大文字と小文字を区別しません。

エラーが該当するhandler_statements内部で新たに発生した時、EXCEPTION句はそのエラーを捕捉できずエラーは外側に波及します。 なお、外側のEXCEPTION句は、そのエラーを捕捉できます。

EXCEPTION句がエラーを捕捉した時、PL/pgSQL関数のローカル変数はエラーが起こった後の状態を保ちます。 しかし、ブロック内部における永続的なデータベースの状態は、ロールバックされます。 そのような例を以下に示します。

    INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
    BEGIN
        UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
        x := x + 1;
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN
            RAISE NOTICE 'caught division_by_zero';
            RETURN x;
    END;

制御が変数yの代入に移ると、ゼロで割るためdivision_by_zeroエラーとなり、EXCEPTION句がそのエラーを捕捉します。 RETURN文による関数の戻り値は、1を加算した後のxの値となりますが、UPDATEコマンドによる結果はロールバックされます。 しかし、BEGINブロックの前のINSERTコマンドはロールバックされません。 したがって、データベースの内容の最終結果はTom Jonesであり、Joe Jonesではありません。

ティップ: EXCEPTION句を含んだブロックの実行に要する時間は、含まないブロックに比べてとても長くなります。 したがって、必要のない時にEXCEPTIONを使用してはいけません。

例外ハンドラの内部では、SQLSTATE変数が起こった例外に対応したエラーコードを保有します。 (表A-1のエラーコード表を参照してください)。 SQLERRM変数は例外に対応したエラーメッセージを保有します。 これらの変数は、例外ハンドラの外部では定義されていません。

例 38-1. UPDATE/INSERTの例外

これはUPDATEまたはINSERTの実行における例外処理を使用した適当な例題です。

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- 最初にキーを更新する
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- キーが存在しないので、キーの挿入を試行する
        -- 他者がすでに同一のキーを挿入していたならば
        -- 一意性に違反する欠陥となります
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
        -- 何もしないで、更新を再試行します
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

アダルトレンタルサーバー