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

38.4. INSERT UPDATE DELETE についてのルール

INSERT UPDATE DELETE に定義するルールは前節で解説したビューのルールとはまったく異なります。 第一点として、これらの CREATE RULE コマンドでは以下を行うことができます。

第二点として、その場所で問い合わせツリーを変更しません。 その代わりに新規の0個以上の問い合わせツリーを生成して、オリジナルを破棄することができます。

38.4.1. 更新ルールの動作

CREATE [ OR REPLACE ] RULE 

name

 AS ON 

event


    TO 

table

 [ WHERE 

condition

 ]
    DO [ ALSO | INSTEAD ] { NOTHING | 

command

 | ( 

command

 ; 

command

 ... ) }

上記の構文を覚えておいてください。 以下では、 更新ルール INSERT UPDATE DELETE に定義されたルールを意味します。

更新ルールは、問い合わせツリーの結果リレーションとコマンド種類が CREATE RULE で与えられるオブジェクトとイベントと等しい場合にルールシステムによって適用されます。 更新ルールに対してルールシステムは問い合わせツリーのリストを生成します。 最初は問い合わせツリーリストは空です。 0( NOTHING キーワード)、1つまたは複数のアクションが有効です。 簡単にするため、ここでは1つのアクションのルールを取り上げます。 このルールは条件を持っていても持っていなくても構いませんし、また INSTEAD ALSO (デフォルト)のいずれかを取ることができます。

ルール条件とはどんなものでしょうか。 それはルールのアクションを行わなければならない時と、行ってはならない時を指定する条件です。 基本的に(特別な意味合いを持った)オブジェクトとして与えられるリレーションである NEW 疑似リレーションか OLD 疑似リレーション、または、その両者のみをこの条件は参照することができます。

1アクションのルールに対し、以下の問い合わせツリーを生成する3つの場合があります。

ALSO または INSTEAD で条件がない場合。

元の問い合わせツリーの条件が追加された、ルールアクションからの問い合わせツリー

条件付き、かつ ALSO

ルール条件と元の問い合わせツリーの条件が追加された、ルールアクションからの問い合わせツリー

条件付き、かつ INSTEAD

ルール条件と元の問い合わせツリーの条件が追加された、ルールアクションからの問い合わせツリーとルール条件の否定条件が追加された元の問い合わせツリー

最後に、もしルールが ALSO の場合、変更されていない元の問い合わせツリーがリストに付け加えられます。 条件付きの INSTEAD ルールのみが既に元の構文解析ツリーに追加をしているので、最後は1つのアクションを持つルールに対して1つもしくは2つの問い合わせツリーにたどり着きます。

ON INSERT ルールでは、元の問い合わせは、( INSTEAD により止められていない限り)ルールによって追加されたアクションの前に実行されます。 これにより、アクションは挿入された行(複数可)を参照することができます。 しかし、 ON UPDATE ON DELETE ルールでは、元の問い合わせはルールによって追加されたアクションの後に実行されます。 これは、アクションが更新される予定の、または削除される予定の行を参照できることを保証します。 さもないと、条件に一致する行を見つけることができないためにアクションが作動しなくなる可能性が起こります。

ルールアクションで生成された問い合わせツリーは、再度書き換えシステムに渡され、相当数の問い合わせツリーの結果をもたらす、より多くのルールの適用を受けることもあります。 ですから、ルールのアクションはルール自身とは異なるコマンド種類か、異なる結果リレーションを持っていなければなりません。 さもないと、この再帰的処理により無限ループに陥ってしまいます。 (ルールの再帰展開は検知され、エラーとして報告されます。)

pg_rewrite システムカタログのアクションにある問い合わせツリーは単なるテンプレートです。 これらは NEW OLD に対する範囲テーブルの項目を参照することができるため、使用される前に何らかの置換措置がとられていなければなりません。 NEW を参照する全てに対し、元の問い合わせの目的リストは対応する項目があるかどうか検索されます。 項目が見つかった場合には、その項目式が参照を置き換えます。 項目がなかった場合、 NEW OLD と同じ意味になる( UPDATE の場合)か、NULLによって置き換えられます( INSERT の場合)。 OLD に対する参照は全て結果リレーションである範囲テーブルの項目への参照に置き換えられます。

更新ルールの適用が終わると、システムはそこで作られた構文解析ツリーにビュールールを適用します。 ビューは、新しい更新アクションを挿入できないため、ビュー書き換えの結果に更新ルールを適用する必要はありません。

38.4.1.1. 最初のルール、ステップバイステップ

shoelace_data リレーションの sl_avail 列の変化を追跡してみたいと思います。 そこでログ用テーブルと、 shoelace_data に対して行われる UPDATE をログに記録するルールを用意しました。

CREATE TABLE shoelace_log (
    sl_name    text,          -- 変更された靴紐
    sl_avail   integer,       -- 新しい現在値
    log_who    text,          -- 誰が行ったか
    log_when   timestamp      -- いつ行ったか
);

CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
    WHERE NEW.sl_avail <> OLD.sl_avail
    DO INSERT INTO shoelace_log VALUES (
                                    NEW.sl_name,
                                    NEW.sl_avail,
                                    current_user,
                                    current_timestamp
                                );

ここで誰かが以下を実行します。

UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';

ログテーブルを見てみましょう。

SELECT * FROM shoelace_log;

 sl_name | sl_avail | log_who | log_when                        
---------+----------+---------+----------------------------------
 sl7     |        6 | Al      | Tue Oct 20 16:14:45 1998 MET DST
(1 row)

思った通りの結果が出ました。 以下に裏で何が起こったのかを説明します。 パーサがまず以下の構文解析ツリーを生成しました。

UPDATE shoelace_data SET sl_avail = 6
  FROM shoelace_data shoelace_data
 WHERE shoelace_data.sl_name = 'sl7';

以下のルール条件式

NEW.sl_avail <> OLD.sl_avail

および、以下のアクションを持つ ON UPDATE log_shoelace ルールがあります。

INSERT INTO shoelace_log VALUES (
       new.sl_name, new.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old;

(通常、 INSERT ... VALUES ... FROM を書くことはできないのでちょっと奇妙に見えるかもしれません。 ここの FROM 句は単に new old の問い合わせツリーの範囲テーブル項目があることを示しているだけです。 これらは、 INSERT コマンドの問い合わせツリー中の変数から参照されるために必要なのです。)

このルールは条件付きの ALSO ルールですので、ルールシステムは変更されたルールアクションと元の問い合わせツリーという2つの問い合わせツリーを返さなければなりません。 第1の段階で元の問い合わせの範囲テーブルはルールアクション問い合わせツリーに取り込まれます。 そして、次の結果を生みます。

INSERT INTO shoelace_log VALUES (
       new.sl_name, new.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       
shoelace_data shoelace_data
;

第2段階で、以下のようにルール条件が付け加えられます。 これにより、この結果集合は sl_avail が変更した行に限定されます。

INSERT INTO shoelace_log VALUES (
       new.sl_name, new.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data
 
WHERE new.sl_avail <> old.sl_avail
;

INSERT ... VALUES WHERE 句を持たないため、これはさらに奇妙です。 しかし、プランナとエクゼキュータには問題はありません。 これらはどのみち INSERT ... SELECT のために同じ機能をサポートしなければなりません。)

第3段階で、以下のように元の問い合わせツリーの条件が付け加えられ、結果集合は元の問い合わせで変更された行のみにさらに限定されます。

INSERT INTO shoelace_log VALUES (
       new.sl_name, new.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data
 WHERE new.sl_avail <> old.sl_avail
   
AND shoelace_data.sl_name = 'sl7'
;

第4段階では、以下のように元の問い合わせツリーの目的リスト項目、または結果リレーションの該当する変数参照で、 NEW の参照を置換します。

INSERT INTO shoelace_log VALUES (
       
shoelace_data.sl_name
, 
6
,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data
 WHERE 
6
 <> old.sl_avail
   AND shoelace_data.sl_name = 'sl7';

第5段階は、以下のように OLD 参照を結果リレーション参照に置き換えます。

INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data
 WHERE 6 <> 
shoelace_data.sl_avail

   AND shoelace_data.sl_name = 'sl7';

これで終わりです。このルールは ALSO のため、元の問い合わせツリーも出力します。 まとめると、ルールシステムからの出力は以下の文に対応する2つの問い合わせツリーのリストです。

INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE 6 <> shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';

UPDATE shoelace_data SET sl_avail = 6
 WHERE sl_name = 'sl7';

この2つは順番通りに処理され、正確にルールが定義した通りです。

元の問い合わせが例えば下記のような場合に、置換と追加された条件は、ログには何も書かれないことを確実にします。

UPDATE shoelace_data SET sl_color = 'green'
 WHERE sl_name = 'sl7';

この場合、元の問い合わせツリーの目的リストには sl_avail の項目がありませんので、 NEW.sl_avail shoelace_data.sl_avail に置き換えられます。 その結果、このルールによって以下のような特別の問い合わせが生成されます。

INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 
shoelace_data.sl_avail
,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE 
shoelace_data.sl_avail
 <> shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';

そしてこの条件は決して真にはなりません。

もし元の問い合わせが複数の行を変更してもうまくいきます。 ですから、誰かが下記のようなコマンドを実行したとします。

UPDATE shoelace_data SET sl_avail = 0
 WHERE sl_color = 'black';

この場合、実際には4行が更新されます( sl1 sl2 sl3 および sl4 )。 しかし s13 は既に sl_avail = 0 を持っています。 この場合、元の問い合わせツリーの条件を満たさず、その結果、以下のような特別の問い合わせツリーがルールによって生成されます。

INSERT INTO shoelace_log
SELECT shoelace_data.sl_name, 0,
       current_user, current_timestamp
  FROM shoelace_data
 WHERE 0 <> shoelace_data.sl_avail
   AND 
shoelace_data.sl_color = 'black'
;

この構文解析ツリーは確実に3つの新しいログ項目を挿入します。 これはまったく正しい動作です [訳注:s13行はWHERE 0 != shoelace_data.sl_avail条件を満たさない(0!=0)ので、実際に更新される4行-1の3行分のログ項目が挿入されます]。

ここで元の構文解析ツリーが最後に実行されるということが重要な理由がわかります。 もし UPDATE が先に実行されたとしたら、全ての行は0にセットされ、 0 <> shoelace_data.sl_avail である行をログ書き込み時の INSERT の段階で見つけられなくなります。

38.4.2. ビューとの協調

誰かがビューに対して INSERT UPDATE DELETE を発行するといった、前述の可能性からビューリレーションを保護する簡単な方法は、それらの構文解析ツリーを破棄してしまうことです。 このために以下のルールを作ることができます。

CREATE RULE shoe_ins_protect AS ON INSERT TO shoe
    DO INSTEAD NOTHING;
CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe
    DO INSTEAD NOTHING;
CREATE RULE shoe_del_protect AS ON DELETE TO shoe
    DO INSTEAD NOTHING;

誰かが shoe ビューリレーションに上記の操作を行おうとすると、ルールシステムはルールを適用します。 ルールにはアクションがなく、かつ、 INSTEAD ですから、結果の問い合わせツリーリストは空になります。 ルールシステムの処理が完了した後に最適化されるものや実行されるべきものが何も残っていませんので、問い合わせ全体が無効になります。

より洗練されたルールシステムの使用法は、実テーブルに適当な操作を行う問い合わせツリーへの書き換えを行うルールを作ることです。 shoelace ビューにこれを適用するために以下のルールを作ります。

CREATE RULE shoelace_ins AS ON INSERT TO shoelace
    DO INSTEAD
    INSERT INTO shoelace_data VALUES (
           NEW.sl_name,
           NEW.sl_avail,
           NEW.sl_color,
           NEW.sl_len,
           NEW.sl_unit
    );

CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
    DO INSTEAD
    UPDATE shoelace_data
       SET sl_name = NEW.sl_name,
           sl_avail = NEW.sl_avail,
           sl_color = NEW.sl_color,
           sl_len = NEW.sl_len,
           sl_unit = NEW.sl_unit
     WHERE sl_name = OLD.sl_name;

CREATE RULE shoelace_del AS ON DELETE TO shoelace
    DO INSTEAD
    DELETE FROM shoelace_data
     WHERE sl_name = OLD.sl_name;

このビュー上で RETURNING 問い合わせをサポートしたい場合、ビューの行を計算する RETURNING 句を含むルールを作成しなければなりません。 これは通常、単一テーブルに対するビューでは非常に簡単ですが、 shoelace のようなビューを結合する場合は多少やっかいです。 挿入する場合を例として以下に示します。

CREATE RULE shoelace_ins AS ON INSERT TO shoelace
    DO INSTEAD
    INSERT INTO shoelace_data VALUES (
           NEW.sl_name,
           NEW.sl_avail,
           NEW.sl_color,
           NEW.sl_len,
           NEW.sl_unit
    )
    RETURNING
           shoelace_data.*,
           (SELECT shoelace_data.sl_len * u.un_fact
            FROM unit u WHERE shoelace_data.sl_unit = u.un_name);

この1つのルールが、ビューに対する INSERT 問い合わせと INSERT RETURNING 問い合わせルールをサポートすることに注意してください。 INSERT では RETURNING 句が無視されるだけです。

ここで店には不定期に靴紐のケースが分厚い送り状とともに届けられると仮定します。 しかし、毎回毎回手作業で shoelace ビューを更新したくはありません。 代わりに、送り状から品目を挿入するテーブルと特殊な仕掛けを持つテーブルの2つの小さなテーブルを用意します。 以下はそれらを作成するコマンドです。

CREATE TABLE shoelace_arrive (
    arr_name    text,
    arr_quant   integer
);

CREATE TABLE shoelace_ok (
    ok_name     text,
    ok_quant    integer
);

CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok
    DO INSTEAD
    UPDATE shoelace
       SET sl_avail = sl_avail + NEW.ok_quant
     WHERE sl_name = NEW.ok_name;

これで、送り状のデータを shoelace_arrive テーブルに投入することができます。

SELECT * FROM shoelace_arrive;

 arr_name | arr_quant
----------+-----------
 sl3      |        10
 sl6      |        20
 sl8      |        20
(3 rows)

そして現在のデータをチェックします。

SELECT * FROM shoelace;

 sl_name  | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
----------+----------+----------+--------+---------+-----------
 sl1      |        5 | black    |     80 | cm      |        80
 sl2      |        6 | black    |    100 | cm      |       100
 sl7      |        6 | brown    |     60 | cm      |        60
 sl3      |        0 | black    |     35 | inch    |      88.9
 sl4      |        8 | black    |     40 | inch    |     101.6
 sl8      |        1 | brown    |     40 | inch    |     101.6
 sl5      |        4 | brown    |      1 | m       |       100
 sl6      |        0 | brown    |    0.9 | m       |        90
(8 rows)

さて、届いた靴紐を移動します。

INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;

そして結果を確認します。

SELECT * FROM shoelace ORDER BY sl_name;

 sl_name  | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
----------+----------+----------+--------+---------+-----------
 sl1      |        5 | black    |     80 | cm      |        80
 sl2      |        6 | black    |    100 | cm      |       100
 sl7      |        6 | brown    |     60 | cm      |        60
 sl4      |        8 | black    |     40 | inch    |     101.6
 sl3      |       10 | black    |     35 | inch    |      88.9
 sl8      |       21 | brown    |     40 | inch    |     101.6
 sl5      |        4 | brown    |      1 | m       |       100
 sl6      |       20 | brown    |    0.9 | m       |        90
(8 rows)

SELECT * FROM shoelace_log;

 sl_name | sl_avail | log_who| log_when                        
---------+----------+--------+----------------------------------
 sl7     |        6 | Al     | Tue Oct 20 19:14:45 1998 MET DST
 sl3     |       10 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 sl6     |       20 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 sl8     |       21 | Al     | Tue Oct 20 19:25:16 1998 MET DST
(4 rows)

1つの INSERT ... SELECT からこの結果になるには、長い道のりがあります。 本章での問い合わせツリーの変形に関する説明はこれが最後です。 まず、以下のようなパーサの出力があります。

INSERT INTO shoelace_ok
SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok;

最初の shoelace_ok_ins ルールが適用され、結果は以下のようになります。

UPDATE shoelace
   SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
       shoelace_ok old, shoelace_ok new,
       shoelace shoelace
 WHERE shoelace.sl_name = shoelace_arrive.arr_name;

そして、 shoelace_ok に対する元の INSERT を破棄します。 この書き換えられた問い合わせは再びルールシステムに渡されて、2番目に適用される shoelace_upd ルールは以下を生成します。

UPDATE shoelace_data
   SET sl_name = shoelace.sl_name,
       sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant,
       sl_color = shoelace.sl_color,
       sl_len = shoelace.sl_len,
       sl_unit = shoelace.sl_unit
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
       shoelace_ok old, shoelace_ok new,
       shoelace shoelace, shoelace old,
       shoelace new, shoelace_data shoelace_data
 WHERE shoelace.sl_name = shoelace_arrive.arr_name
   AND shoelace_data.sl_name = shoelace.sl_name;

これは再び INSTEAD ルールですので、以前の問い合わせツリーは破棄されます。 この問い合わせは shoelace ビューを引き続き使用していることに注意してください。 しかし、この段階ではルールシステムは終了していないため、引き続き _RETURN ルールが適用され、下記のようになります。

UPDATE shoelace_data
   SET sl_name = s.sl_name,
       sl_avail = s.sl_avail + shoelace_arrive.arr_quant,
       sl_color = s.sl_color,
       sl_len = s.sl_len,
       sl_unit = s.sl_unit
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
       shoelace_ok old, shoelace_ok new,
       shoelace shoelace, shoelace old,
       shoelace new, shoelace_data shoelace_data,
       shoelace old, shoelace new,
       shoelace_data s, unit u
 WHERE s.sl_name = shoelace_arrive.arr_name
   AND shoelace_data.sl_name = s.sl_name;

最後に、 log_shoelace ルールが適用され、以下のような特別な問い合わせツリーが生成されます。

INSERT INTO shoelace_log
SELECT s.sl_name,
       s.sl_avail + shoelace_arrive.arr_quant,
       current_user,
       current_timestamp
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
       shoelace_ok old, shoelace_ok new,
       shoelace shoelace, shoelace old,
       shoelace new, shoelace_data shoelace_data,
       shoelace old, shoelace new,
       shoelace_data s, unit u,
       shoelace_data old, shoelace_data new
       shoelace_log shoelace_log
 WHERE s.sl_name = shoelace_arrive.arr_name
   AND shoelace_data.sl_name = s.sl_name
   AND (s.sl_avail + shoelace_arrive.arr_quant) <> s.sl_avail;

この後、ルールシステムはルールを使い切り、生成された問い合わせツリーを返します。

そして、以下の SQL 文と等価となる2つの最終問い合わせツリーで終結します。

INSERT INTO shoelace_log
SELECT s.sl_name,
       s.sl_avail + shoelace_arrive.arr_quant,
       current_user,
       current_timestamp
  FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data,
       shoelace_data s
 WHERE s.sl_name = shoelace_arrive.arr_name
   AND shoelace_data.sl_name = s.sl_name
   AND s.sl_avail + shoelace_arrive.arr_quant <> s.sl_avail;

UPDATE shoelace_data
   SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant
  FROM shoelace_arrive shoelace_arrive,
       shoelace_data shoelace_data,
       shoelace_data s
 WHERE s.sl_name = shoelace_arrive.sl_name
   AND shoelace_data.sl_name = s.sl_name;

結果は、1つのリレーションから来たデータが別のリレーションに挿入され、3つ目のリレーションの更新に変更され、4つ目の更新と5つ目への最終更新のログ記録に変更され、最終的に2つの問い合わせに縮小されます。

ちょっと見苦しい小さな事項があります。 でき上がった2つの問い合わせを見ると、1つに縮小されたはずの shoelace_data リレーションが範囲テーブルに二度出てきます。 プランナは処理をしないので、 INSERT のルールシステムの出力に対する実行計画は次のようになります。

入れ子状ループ
  ->  マージ結合
        ->  シーケンシャルスキャン
              ->  ソート
                    ->  sに対するシーケンシャルスキャン
        ->  シーケンシャルスキャン
              ->  ソート
                    ->  shoelace_arriveに対するシーケンシャルスキャン
  ->  shoelace_dataに対するシーケンシャルスキャン

一方、余計な範囲テーブル項目を省略することで、以下のようにログテーブルにまったく同じ項目が作られます。

マージ結合
  ->  シーケンシャルスキャン
        ->  ソート
              ->  sに対するシーケンシャルスキャン
  ->  シーケンシャルスキャン
        ->  ソート
              ->  shoelace_arriveに対するシーケンシャルスキャン

ですから、ルールシステムは、まったく必要のない shoelace_data リレーションに対する余計なスキャンを一度行うことになります。 そして UPDATE でも同様な不要なスキャンが再度実行されます。 しかしながら、これらを全て可能にするのは大変な仕事です。

最後に PostgreSQL のルールシステムとその効力を示しましょう。 例えば、まったく売れそうもない靴紐をデータベースに追加してみます。

INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);

全ての靴に合わない色が shoelace 項目にあるかどうかを検査するビューを作成したいと考えます。 ビューは以下のようになります。

CREATE VIEW shoelace_mismatch AS
    SELECT * FROM shoelace WHERE NOT EXISTS
        (SELECT shoename FROM shoe WHERE slcolor = sl_color);

この出力は以下のようになります。

SELECT * FROM shoelace_mismatch;

 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
---------+----------+----------+--------+---------+-----------
 sl9     |        0 | pink     |     35 | inch    |      88.9
 sl10    |     1000 | magenta  |     40 | inch    |     101.6

ここで、合う靴がない靴紐のうち、在庫がないものをデータベースから削除するように設定してみます。 これは PostgreSQL では困難な作業ですので、直接削除しません。 代わりに、以下のようにもう1つビューを作成します。

CREATE VIEW shoelace_can_delete AS
    SELECT * FROM shoelace_mismatch WHERE sl_avail = 0;

そして、以下を行います。

DELETE FROM shoelace WHERE EXISTS
    (SELECT * FROM shoelace_can_delete
             WHERE sl_name = shoelace.sl_name);

さあできました。

SELECT * FROM shoelace;

 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
---------+----------+----------+--------+---------+-----------
 sl1     |        5 | black    |     80 | cm      |        80
 sl2     |        6 | black    |    100 | cm      |       100
 sl7     |        6 | brown    |     60 | cm      |        60
 sl4     |        8 | black    |     40 | inch    |     101.6
 sl3     |       10 | black    |     35 | inch    |      88.9
 sl8     |       21 | brown    |     40 | inch    |     101.6
 sl10    |     1000 | magenta  |     40 | inch    |     101.6
 sl5     |        4 | brown    |      1 | m       |       100
 sl6     |       20 | brown    |    0.9 | m       |        90
(9 rows)

合計4つのネスト/結合されたビューを副問い合わせの条件として持ち、その中の1つはビューを含む副問い合わせ条件を持ち、かつ演算を施されたビューの列が使われる場合の、ビューに対する DELETE が、実テーブルから要求されたデータを削除する単一の問い合わせツリーに書き換えられます。

このような構造が必要な状況は実社会ではほとんどないと思われます。 しかし、実際に動くことを確認できれば安心できます。


powered by SEO.CUG.NET