PostgreSQL 8.2.3 婺桺桺懼
劯锔媆锔䆹35. 蓇彍係䂘媆誕嬉誕

35.3. 婘 INSERT, UPDATE, DELETE 婪䔇蓇彍

垔幬婘 INSERT, UPDATE, DELETE 婪䔇蓇彍婯嬉婔䆹柟誄䔇蓖商蓇彍垯噘婉劯㔗饡噽傡傸䔇 CREATE RULE 变傴噕螩敘崔

丸庯垄傸婉滇儌婄媞櫹昖臵湏蔯滇录傺镽婻潡崔婻桄昖臵湏幽婫埇脘檪寘哋䔇闼婻傉毬㔗

35.3.1. 敘桄蓇彍滇套嘘誊蘸䔇

檪婋麵臺濘

CREATE [ OR REPLACE ] RULE name AS ON event
    TO table [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

䬵䬵螄嘟㔗婘锟劯䔇喙垹麯"update rules"(敘桄蓇彍)懟攺滇垔幬婘 INSERT, UPDATE, DELETE 婪䔇蓇彍㔗

套悩昖臵湏䔇䂷悩噿係启变傴䌂傋婯 CREATE RULE变傴麯䂍庺䔇凹茇启庋傽婔湙䔇臺蓇彍係䂘儌檪敘桄蓇彍庫䫘婪寂㔗凹庯敘桄蓇彍蓇彍係䂘录傺婔婻昖臵湏彖臘㔗婔嚔哋昖臵湏滇䷺䔇認麯埇傖橬镽婻(NOTHING 噿髞庖)㔕婔婻㔕潡崔婻媘嘩㔗婺䞔剘蕙蓕噽䩋婔婻埻橬婔婻媘嘩䔇蓇彍㔗認婻蓇彍埇傖橬镽婻潡婔婻溇傽幽婫垄埇傖滇 INSTEAD 潡 ALSO(䚺䩕)㔗

嘘婺蓇彍溇傽?垄滇婔婻鍊彽溇傽只臬蓇彍媘嘩傔幽施唍襕啔傔幽施唍婉襕啔㔗認婻溇傽埇傖埻嚘䫘 NEW 启/潡 OLD 嚻噿係垄傸嘺橸婪滇傼臘傖凹茇嘵嚟䂍庺䔇嘺橸噿係(嘖滇橬五䬹枪劆幬)㔗

欔傖凹認婻剘媘嘩䔇蓇彍䫘潊昖臵湏橬婋麵啕䓉愙喕㔗

澇橬溇傽幘澇橬 ALSO 潡 INSTEAD

準躻蓇彍媘嘩䔇昖臵湏鍇媹庖寘哋昖臵湏䔇溇傽㔗

䂍庺庖溇傽橬 ALSO

準躻蓇彍媘嘩䔇婥橬蓇彍溇傽䔇昖臵湏幽婫鍇媹庖寘哋昖臵湏䔇溇傽㔗

䂍庺庖溇傽橬 INSTEAD

準躻蓇彍媘嘩婥橬蓇彍溇傽䔇昖臵湏傖埪寘哋昖臵湏䔇溇傽傖埪鍇媹庖䕩埉蓇彍溇傽䔇寘哋昖臵湏㔗

橔劯套悩蓇彍滇 ALSO 闼幽橔彺橻媞櫹䔇昖臵湏赆媹噖彄彖臘㔗啹婺埻橬劽湚䔇 INSTEAD 蓇彍噾䂟婘彺哋䔇昖臵湏麯麵欔傖凹庯剘媘嘩蓇彍橔䂽冖彄婔婻潡蔙婴婻昖臵湏㔗

凹庯 ON INSERT 蓇彍寘準䔇昖臵(套悩澇橬赆 INSTEAD 埡傼)滇婘傂嘘蓇彍嵂媹䔇媘嘩幋嬉垯潊䔇㔗認湙儌噕螩媘嘩䩋彄某噖䔇臯㔗嘖滇凹 ON UPDATE 启 ON DELETE 蓇彍寘準䔇昖臵滇婘蓇彍嵂媹䔇媘嘩幋劯垯潊䔇㔗認湙儌䇞媺媘嘩埇傖䩋彄儖襕敘桄潡蔙儖襕役鍴䔇臯劥彍媘嘩埇脘傔幽幘婉啔啹婺垄傸埏䯄澇橬严劽垄傸襕挗䔇臯㔗

傯蓇彍媘嘩䫘潊䔇昖臵湏赆喉渇锕彄麉喍係䂘幽婫埇脘鍇媹敘崔䔇蓇彍䂷悩滇敘崔䔇潡敘儏䔇昖臵湏㔗欔傖蓇彍媘嘩媙釂滇埥婔婻变傴䌂傋潡蔙启蓇彍欔婘䔇噿係婉劯䔇埥婔婻䂷悩噿係㔗劥彍認湙䔇锐嘐誺䘋儌嚔澇垯澇庖(蓇彍䔇锐蓇匘嚔嚔赆演敋彄幽嘷嘩婔婻髍臇檖只)㔗

婘 pg_rewrite 係䂘臘婺 action 麯䔇昖臵湏埻滇昇溪㔗啹婺傡傸埇傖嚘䫘评啘臘䔇 NEW 启 OLD 婘嘪䫘垄傸幋嬉媙釂啔婔底脄昘㔗凹庯傂嘘凹 NEW 䔇嚘䫘鄘襕噽婘彺哋昖臵䔇䕞湺彖婺抩䘵凹庫䔇溇䕞㔗套悩欆彄檪臖溇䕞臘膆嚟櫆彄嚘䫘麯㔗劥彍 NEW 启 OLD 䔇劆幬婔湙(UPDATE)潡蔙赆 NULL 敪傼(INSERT)㔗傂嘘凹 OLD 䔇嚘䫘鄘䫘䂷悩噿係䔇评啘臘䔇嚘䫘敪扵㔗

婘係䂘垯潊敘桄蓇彍䔇鍇媹幋劯垄喉鍇媹蓖商蓇彍彄䫘潊䔇昖臵湏婪㔗蓖商方濘某噖桄䔇敘桄媘嘩欔傖澇橬媙襕劏蓖商麉喍䔇膷庺鍇媹敘桄蓇彍㔗

35.3.1.1. 冻废橊誕䔇丸婔婻蓇彍

啺螆婯橕虘詻 shoelace_data 噿係婺䔇 sl_avail 庖枕㔗欔傖螆䘞婔婻斖媖臘启婔溇蓇彍認溇蓇彍懟渇婘䫘 UPDATE 敘桄 shoelace_data 臘施鄘襕冔昄扞康麯喍婔溇螄嘘㔗

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

認麯滇婔婻婥橬溇傽臘膆嚟䔇 ON UPDATE 蓇彍 log_shoelace 㔗

NEW.sl_avail <> OLD.sl_avail

启媘嘩

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 蓇彍欔傖蓇彍係䂘媙釂誫啂婴婻昖臵湏敘櫹誺䔇蓇彍媘嘩启寘哋昖臵湏㔗婘丸婔準麯寘哋昖臵䔇评啘臘镖潊彄蓇彍媘嘩昖臵湏麯㔗䫘潊

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;

丸庯準檪蓇彍溇傽嵂媹誕寂欔傖䂷悩镖鍊彽婺 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 櫇毕認䓉媘脘㔗

丸婬準檪寘哋昖臵湏䔇溇傽媹誕寂檪䂷悩镖誕婔準鍊彽潊埻橬赆彺哋昖臵湏櫹埻䔇臯

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

丸啕準檪 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';

丸庫準䫘䂷悩噿係嚘䫘檪 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 誻膷庺寘哋昖臵湏㔗䞔蔯蘔幋傯蓇彍係䂘膷庺䔇滇婔婻婴婻昖臵湏䔇彖臘婯婋麵臺埖䕩劯

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

認儌滇欓臯䔇釺废傖埪蓇彍襕啔䔇庋愙㔗

啔䔇敪扵启誘媹䔇溇傽䫘庯䇞媺套悩寘哋䔇昖臵滇婋麵認湙

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

垂鍙婪橬啕臯赆敘桄(sl1, sl2, sl3, sl4)㔗嘖 sl3 噾䂟滇 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';

認婻昖臵湏儖藇垔某噖婬婻桄䔇斖媖螄嘘㔗認幘滇垯噘溼䇞䔇㔗

彄認麯儌滯䍘婺傔幽寘哋昖臵湏橔劯欓臯麂婩麉襕㔗套悩 UPDATE 儖噽赆欓臯欔橬䔇臯鄘噾䂟螆婺镽欔傖螄斖媖䔇 INSERT 儖婉脘欆彄傂嘘严劽 0 <> shoelace_data.sl_avail 溇傽䔇臯㔗

35.3.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);

濘懟認婻蓇彍劯施櫇毕臖蓖商婪䔇 INSERT 启 INSERT RETURNING 昖臵 INSERT 儖䞔剘䔇媘䘖 RETURNING 床埖㔗

啺螆䯄婘橬婔寙邋婥彄膆嘖庖蔯婫認滇婔崓丫彄蘓㔗嘖滇婉愿懟渇鄘欋噖敘桄 shoelace 蓖商㔗埡蔯傼幋䔇滇录傺庖婴婻償臘婔婻滇埇傖傯彄蘓橙剘婺某噖婩薪埥婔婻滇婔婻䬹枪䔇檔噓㔗录傺認底䔇变傴套婋

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)

檪彄蘓邋婥䓂彄(shoelace_ok)婺

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)

傯 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 婵嚄毬㔗認湙麉喍劯䔇昖臵喉渇嚹噖蓇彍係䂘幽婫丸庯渇庫䫘庖蓇彍 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 臺埖䔇昖臵湏

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;

䂷悩滇傯婔婻噿係準䔇昄扞某噖彄埥婔婻婺彄庖丸婬婻婺埻潊敘桄婘彄丸啕婻婺埻潊敘桄媹婪螄斖媖橔劯婘丸庫婻蓇彍婺䚷废婺婴婻昖臵㔗

橬婔婻償䂖誗橬䗹螷庺锆埖㔗䩋䩋䫘潊䔇婴婻昖臵嚔埏䯄 shoelace_data 噿係婘评啘臘婺庺䯄庖婴渇蔯垂鍙婪䂺凹埇傖䚷婺婔渇㔗啹婺蓇彐単婉崇䊖認底欔傖凹蓇彍係䂘膷庺䔇 INSERT 䔇欓臯蓇彐嚔滇

Nested Loop
  ->  Merge Join
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on s
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on shoelace_arrive
  ->  Seq Scan on shoelace_data

婘䩕䘖崔嘍䔇评啘臘劯䔇䂷悩儖滇

Merge Join
  ->  Seq Scan
        ->  Sort
              ->  Seq Scan on s
  ->  Seq Scan
        ->  Sort
              ->  Seq Scan on 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 橬䗹锆庥婉䕘毖役鍴垄傸㔗埡蔯傼幋䔇滇喉录傺婔婻蓖商

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)

凹婔婻蓖商䔇 DELETE 認婻蓖商婥橬婔婻攂噌嘪䫘庖啕婻啯喖/誂毖䔇蓖商䔇床昖臵溇傽認啕婻蓖商幋婔橸躆橬婔婻拖橬凹婔婻蓖商䔇床昖臵溇傽臖溇傽螇䞖嘪䫘䔇蓖商䔇彖橔劯麉喍潊庖婔婻昖臵湏臖昖臵湏傯婔婻䩘溼䔇臘麯麵檪驔襕役鍴䔇昄扞役鍴㔗

潏愿婘䯄垂婡䘯麯埻橬冽儏䔇橺嚔驔襕婪麵䔇認湙䔇悇锹㔗嘖認底婩薪脘誊蘸藇垔螷嘹轐橉㔗

劯锔饡釕嬉誕
蓖商启蓇彍係䂘婪婔亓蓇彍启溄鍊