無料スクリプト配布のPHP.TO   PHPの実用的なtips PHPマニュアル MySQLマニュアル Apacheマニュアル PostgreSQLマニュアル マニュアル検索    

40.6. 制御構造

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

40.6.1. 関数からの復帰

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

40.6.1.1. RETURN

RETURN 

expression

;

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

スカラ型を返す関数の内部では、代入のところで説明したように、式の結果は自動的に関数の戻り値の型にキャストされます。 しかし、複合(行)値を返すためには、要求された列集合を正確に渡す式を記述しなければなりません。 これは、明示的なキャストの使用が必要ということです。

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

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

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

例を示します。

-- スカラー型を返す関数
RETURN 1 + 2;
RETURN scalar_var;

-- 複合型を返す関数
RETURN composite_type_var;
RETURN (1, 2, 'three'::text);  -- 列を正しい型にキャストしなければなりません

40.6.1.2. RETURN NEXT および RETURN QUERY

RETURN NEXT 

expression

;
RETURN QUERY 

query

;
RETURN QUERY EXECUTE 

command-string

 [
 USING 

expression

 [
, ... 
] 
];

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

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

RETURN QUERY には RETURN QUERY EXECUTE という亜種があり、それは問い合わせが動的に実行されることを指定します。 パラメータ式を、 EXECUTE コマンド内と全く同じように、 USING によって演算された問い合わせ文字列に挿入することができます。

出力パラメータを持つ関数を宣言した時は、式の無い 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 get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN
        SELECT * FROM foo WHERE fooid > 0
    LOOP
        -- ここで何か処理を実行できます
        RETURN NEXT r; -- 現在の SELECT 行を返します
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_foo();

RETURN QUERY を使用した例を示します

CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY SELECT flightid
                   FROM flight
                  WHERE flightdate >= $1
                    AND flightdate < ($1 + 1);

    -- 実行は終了していないので、行が返されたかどうか検査することができます
    -- 返されていない場合は、例外を発生させます
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No flight at %.', $1;
    END IF;

    RETURN;
 END
$BODY$
LANGUAGE plpgsql;

-- 利用できるフライトを返します
-- フライトがない場合は、例外を発生させます
SELECT * FROM get_available_flightid(CURRENT_DATE);

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

40.6.2. 条件分岐

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

  • IF ... THEN

  • IF ... THEN ... ELSE

  • IF ... THEN ... ELSE IF

  • IF ... THEN ... ELSIF ... THEN ... ELSE

また、以下のような2つの CASE の形式があります。

  • CASE ... WHEN ... THEN ... ELSE ... END CASE

  • CASE WHEN ... THEN ... ELSE ... END CASE

40.6.2.1. IF-THEN

IF 

boolean-expression

 THEN
    

statements


END IF;

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

例:

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

40.6.2.2. IF-THEN-ELSE

IF 

boolean-expression

 THEN
    

statements


ELSE
    

statements


END IF;

IF-THEN-ELSE 文は IF-THEN に加え、条件評価が偽の場合に実行すべき代替となる文の集合を指定することができます。 (これには条件がNULLと評価した場合も含まれることに注意してください。)

例:

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;

40.6.2.3. IF-THEN-ELSIF

IF 

boolean-expression

 THEN
    

statements


[
 ELSIF 

boolean-expression

 THEN
    

statements


[
 ELSIF 

boolean-expression

 THEN
    

statements


    ...
]
]
[
 ELSE
    

statements

 
]
END IF;

選択肢が2つだけではなくより多くになる場合があります。 IF-THEN-ELSIF は、順番に複数の代替手段を検査する、より便利な方法を提供します。 IF 条件は最初の真である結果が見つかるまで連続して検査されます。 そして関連した文が実行され、その後 END IF 以降の次の文に制御が渡されます。 (以降にある IF 条件の検査はすべて実行 されません 。) 全ての IF 条件が真でない場合、 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;

ELSIF キーワードは ELSEIF のように書くことができます。

同じ作業を遂行する別の方法は、以下の例のように IF-THEN-ELSE 文を入れ子にすることです。

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

しかし、この方法はそれぞれの IF に対応する END IF の記述が必要です。 従って、多くの選択肢がある場合 ELSIF を使用するよりも厄介です。

40.6.2.4. 単純な CASE

CASE 

search-expression


    WHEN 

expression

 [
, 

expression

 [
 ... 
]
] THEN
      

statements


  [
 WHEN 

expression

 [
, 

expression

 [
 ... 
]
] THEN
      

statements


    ... 
]
  [
 ELSE
      

statements

 
]
END CASE;

CASE の単純な形式はオペランドの等価性にもとづく条件的実行を提供します。 search-expression は(一度だけ)評価され、その後 WHEN 句内のそれぞれの expression と比較されます。 一致するものが見つかると、関連した statements が実行され、 END CASE の次の文に制御が渡されます。 (以降のく WHEN 式は評価されません。) 一致するものが見つからない場合、 ELSE statements が実行されますが、 ELSE が無いときは CASE_NOT_FOUND 例外を引き起こします。

以下は簡単な例です。

CASE x
    WHEN 1, 2 THEN
        msg := 'one or two';
    ELSE
        msg := 'other value than one or two';
END CASE;

40.6.2.5. 検索付き CASE

CASE
    WHEN 

boolean-expression

 THEN
      

statements


  [
 WHEN 

boolean-expression

 THEN
      

statements


    ... 
]
  [
 ELSE
      

statements

 
]
END CASE;

CASE の検索された形式は論理値式の真の結果に基づく条件付き実行を提供します。 それぞれの WHEN 句の boolean-expression true となる1つが見つかるまで順番に評価されます。 その後、関連する statements が実行され、その結果 END CASE の次の文に制御が渡されます。 (以降の WHEN 式は評価されません。) 真となる結果が見つからない場合、 ELSE statements が実行されますが、 ELSE が存在しないときは CASE_NOT_FOUND 例外を引き起こします。

以下は簡単な例です。

CASE
    WHEN x BETWEEN 0 AND 10 THEN
        msg := 'value is between zero and ten';
    WHEN x BETWEEN 11 AND 20 THEN
        msg := 'value is between eleven and twenty';
END CASE;

この形式の CASE は、判定基準が省略された ELSE 句に達した場合に何もしないのではなくエラーなる点を除き、 IF-THEN-ELSIF と全く同一です。

40.6.3. 単純なループ

LOOP EXIT CONTINUE WHILE FOR FOREACH 文を使用して、 PL/pgSQL 関数で、一連のコマンドを繰り返すことができます。

40.6.3.1. LOOP

[
 <<

label

>> 
]
LOOP
    

statements


END LOOP [
 

label

 
];

LOOP は、 EXIT 文または RETURN 文によって終了されるまで無限に繰り返される、条件なしのループを定義します。 省略可能な label は、入れ子状になったループにおいて EXIT および CONTINUE 文がどのレベルの入れ子を参照するかを指定するために使用されます。

40.6.3.2. EXIT

EXIT [
 

label

 
] [
 WHEN 

boolean-expression

 
];

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

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

EXIT は、すべての種類のループと共に使用できます。 条件なしのループでの使用に限定されません。

BEGIN ブロックと共に使用した時、 EXIT によりブロックの次の文に制御が移ります。 この目的のためにラベルが使用されなければならないことに注意してください。 ラベル無しの EXIT BEGIN ブロックに対応するとは決して考えられません。 (これは、ラベル無しの EXIT BEGIN ブロックに対応することを許容する PostgreSQL の8.4より前のリリースからの変更です。)

例:

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

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

<<ablock>>
BEGIN
    -- 何らかの演算
    IF stocks > 100000 THEN
        EXIT ablock;  -- これによりBEGINブロックを抜け出す
    END IF;
    
    -- stokcs > 100000 であればここでの演算は省略
END;

40.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;

40.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;

40.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 の場合はより小さい)場合、ループ本体はまったく実行されません。 エラーは発生しません。

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

40.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
    RAISE NOTICE 'Refreshing materialized views...';

    FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
        -- ここで"mviews"はcs_materialized_viewsの1つのレコードを持ちます
        RAISE NOTICE 'Refreshing materialized view %s ...', 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;

    RAISE NOTICE 'Done refreshing materialized views.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

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

この種類の FOR 文の query としては、呼び出し元に行を返すSQLコマンドをすべて使用できます。 通常は SELECT ですが、 RETURNING 句を持つ INSERT UPDATE または DELETE も使用できます。 EXPLAIN などのユーティリティコマンドも作動します。

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

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

[
 <<

label

>> 
]
FOR 

target

 IN EXECUTE 

text_expression

 [
 USING 

expression

 [
, ... 
] 
] LOOP
    

statements


END LOOP [
 

label

 
];

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

結果を通して繰り返さなければならない問い合わせを指定するもう1つの方法として、カーソルの宣言があります。 これは 項40.7.4 で説明します。

40.6.5. 配列を巡回

FOREACH ループは FOR ループにとてもよく似ています。 しかし、SQL 問い合わせが抽出した行を繰り返す代わりに、配列の要素を繰り返します。 (一般的に FOREACH は、複合値で表現される構成要素の巡回を意味しますが、配列でない複合値も巡回する亜種が将来は追加されるかもしれません。) 配列を巡回する FOREACH 文を示します。

[
 <<

label

>> 
]
FOREACH 

target

 [
 SLICE 

number

 
] IN ARRAY 

expression

 LOOP
    

statements


END LOOP [
 

label

 
];

SLICE がない、または SLICE 0 が指定された場合、ループは expression によって評価されて作成された配列の各要素を繰り返します。 target 変数が各要素の値に順次割り当てられ、各要素に対してループ本体が実行されます。 整数配列の要素を巡回する例を示します。

CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
DECLARE
  s int8 := 0;
  x int;
BEGIN
  FOREACH x IN ARRAY $1
  LOOP
    s := s + x;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql;

配列の次元数に関係なく、要素は格納した順番で処理されます。 通常 target は単一の変数ですが、複合値(レコード)の配列を巡回するときは、変数のリストも可能です。 その場合、配列の各要素に対して、変数は複合値(レコード)の列から連続的に割り当てられます。

正の SLICE 値を持つ場合、 FOREACH は単一の要素ではなく多次元配列の低次元部分配列を通して繰り返します。 SLICE 値は、配列の次元数より小さい整数定数でなければなりません。 target 変数は配列でなければなりません。 この変数は、配列値から連続した部分配列を受けとります ここで部分配列は SLICE で指定した次数となります。 以下に1次元の部分配列を通した繰り返しの例を示します。

CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
  x int[];
BEGIN
  FOREACH x SLICE 1 IN ARRAY $1
  LOOP
    RAISE NOTICE 'row = %', x;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);

NOTICE:  row = {1,2,3}
NOTICE:  row = {4,5,6}
NOTICE:  row = {7,8,9}
NOTICE:  row = {10,11,12}

40.6.6. エラーの捕捉

デフォルトでは、 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 を含んだ外側のブロックはエラーを捕捉できますが、失敗すると関数の処理は中断されます。

全ての condition の名前は 付録A に示したもののいずれかを取ることができます。 分類名はそこに分類される全てのエラーに合致します。 OTHERS という特別の状態名は QUERY_CANCELED 以外の全てのエラーに合致します。 ( QUERY_CANCELED を名前で捕捉することは可能ですが、賢明ではありません。) 状態名は大文字小文字を区別しません。 同時に、エラー状態は SQLSTATE コードで指定可能です。 例えば以下は等価です。

WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...

エラーが該当する 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 コマンドによる結果はロールバックされます。 しかし、前のブロックの INSERT コマンドはロールバックされません。 したがって、データベースの内容の最終結果は Tom Jones であり、 Joe Jones ではありません。

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

例 40-2. 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');

このコーディングでは unique_violation エラーの原因が INSERT によるものであり、テーブルのトリガ関数内部の INSERT によるものでないと仮定します。 また、テーブルに2つ以上の一意インデックスが存在した場合、どちらのインデックスがエラーの原因になろうと操作を再試行するので、誤作動となります。 捕捉したエラーが予測したものであるか検証するために、次節で記述するエラー情報を利用すれば、より安全となります。

40.6.6.1. エラーに関する情報の取得

例外ハンドラはしばしば、起こった特定のエラーを識別する必要があります。 現在の例外に関する情報を取得する方法は2つあります。 特殊な変数と GET STACKED DIAGNOSTICS コマンドです。

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

例外ハンドラの内部では、 GET STACKED DIAGNOSTICS コマンドを使用して、現在の例外に関する情報を取り出すこともできます。 次のようなやり方となります。

GET STACKED DIAGNOSTICS 

variable

 = 

item

 [
 , ... 
];

item は、指定された変数(これは受け取るために正しいデータ型でなければなりません)に代入される状態値を識別するキーワードです。 現在使用可能なステータス項目は 表40-1 に表示されています。

表 40-1. エラーの診断値

名前 説明
RETURNED_SQLSTATE text 例外のSQLSTATEエラーコード
COLUMN_NAME text 例外に関連した列名
CONSTRAINT_NAME text 例外に関連した制約名
PG_DATATYPE_NAME text 例外に関連したデータ型名
MESSAGE_TEXT text 例外の主要なメッセージのテキスト
TABLE_NAME text 例外に関連したテーブル名
SCHEMA_NAME text 例外に関連したスキーマ名
PG_EXCEPTION_DETAIL text 例外の詳細なメッセージのテキスト、存在する場合
PG_EXCEPTION_HINT text 例外のヒントとなるメッセージのテキスト、存在する場合
PG_EXCEPTION_CONTEXT text 呼び出しスタックを記述するテキストの行

例外が項目の値を設定しない場合、空文字列が返されます。

以下に例を示します。

DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN

  -- 例外を引き起こす処理
  ...
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;


powered by SEO.CUG.NET