INSERT、UPDATE 和 DELETE 規則 #在 INSERT、UPDATE 和 DELETE 上定義的規則與前面章節描述的檢視規則有顯著不同。首先，它們的 CREATE RULE 命令允許更多
它們可以沒有操作。
它們可以有多個操作。
它們可以是 INSTEAD 或 ALSO（預設）。
偽關係 NEW 和 OLD 會變得有用。
它們可以有關聯條件。
其次，它們不會就地修改查詢樹。相反，它們會建立零個或多個新的查詢樹，並可以丟棄原始查詢樹。
在許多情況下，使用規則在 INSERT/UPDATE/DELETE 上執行的任務最好透過觸發器來完成。觸發器的語法稍微複雜一些，但它們的語義更容易理解。當原始查詢包含易變函式時，規則往往會產生令人驚訝的結果：在執行規則的過程中，易變函式可能會執行比預期更多的次數。
此外，有些情況根本不支援這類規則，特別是原始查詢中的 WITH 子句以及 UPDATE 查詢的 SET 列表中的多重賦值子 SELECT。這是因為將這些構造複製到規則查詢中會導致子查詢被多次評估，這與查詢作者的明確意圖相悖。
請牢記語法。
CREATE [ OR REPLACE ] RULEnameAS ONeventTOtable[ WHEREcondition] DO [ ALSO | INSTEAD ] { NOTHING |command| (command;command... ) }
在以下內容中，UPDATE 規則 指的是在 INSERT、UPDATE 或 DELETE 上定義的規則。
當查詢樹的結果關係和命令型別與 CREATE RULE 命令中給出的物件和事件相匹配時，規則系統會應用 UPDATE 規則。對於 UPDATE 規則，規則系統會建立一個查詢樹列表。最初，查詢樹列表是空的。可以有零個（NOTHING 關鍵字）、一個或多個操作。為簡化起見，我們將檢視具有一個操作的規則。此規則可以有關聯條件，也可以沒有，並且可以是 INSTEAD 或 ALSO（預設）。
什麼是規則關聯條件？它是一個限制，用於告訴何時執行規則的操作以及何時不執行。此關聯條件只能引用偽關係 NEW 和/或 OLD，它們基本上代表作為物件給出的關係（但具有特殊含義）。
因此，我們有三種情況，會為單個操作規則產生以下查詢樹。
ALSO 或 INSTEAD來自規則操作的查詢樹，加上原始查詢樹的關聯條件
ALSO來自規則操作的查詢樹，加上規則關聯條件和原始查詢樹的關聯條件
INSTEAD來自規則操作的查詢樹，加上規則關聯條件和原始查詢樹的關聯條件；以及原始查詢樹加上規則關聯條件的否定
最後，如果規則是 ALSO，則將未更改的原始查詢樹新增到列表中。由於只有帶條件的 INSTEAD 規則已經添加了原始查詢樹，因此對於單個操作規則，我們最終得到一個或兩個輸出查詢樹。
對於 ON INSERT 規則，原始查詢（如果未被 INSTEAD 抑制）在應用規則新增的任何操作之前執行。這允許操作看到插入的行。但對於 ON UPDATE 和 ON DELETE 規則，原始查詢在應用規則新增的操作之後執行。這確保了操作可以看到將被更新或刪除的行；否則，由於操作找不到匹配其關聯條件的行，因此可能不會執行任何操作。
從規則操作生成的查詢樹會再次被放入重寫系統，可能會應用更多規則，從而產生更多或更少的查詢樹。因此，規則的操作必須與規則本身具有不同的命令型別或不同的結果關係，否則此遞迴過程將導致無限迴圈。（規則的遞迴展開會被檢測到並報告為錯誤。）
在 pg_rewrite 系統目錄的規則操作中找到的查詢樹只是模板。由於它們可以引用 NEW 和 OLD 的範圍表條目，因此在使用它們之前需要進行一些替換。對於任何對 NEW 的引用，會在原始查詢的目標列表中搜索相應的條目。如果找到，該條目的表示式將替換該引用。否則，NEW 對於 (UPDATE) 意味著與 OLD 相同，或者對於 (INSERT) 被替換為 NULL 值。對 OLD 的任何引用都將被替換為對結果關係範圍表條目的引用。
在系統完成應用 UPDATE 規則後，它會將檢視規則應用於生成的查詢樹。檢視不能插入新的 UPDATE 操作，因此無需將 UPDATE 規則應用於檢視重寫的結果。
假設我們想跟蹤 shoelace_data 關係中 sl_avail 列的更改。因此，我們設定一個日誌表和一個規則，當在 shoelace_data 上執行 UPDATE 時，該規則會條件性地寫入日誌條目。
CREATE TABLE shoelace_log (
sl_name text, -- shoelace changed
sl_avail integer, -- new available value
log_who text, -- who did it
log_when timestamp -- when
);
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';
有一個規則 log_shoelace，它在 ON UPDATE 發生時，帶有規則關聯條件表示式
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。
保護檢視關係免受前面提到的可能性（有人可能嘗試在檢視上執行 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 子句將被忽略。
請注意，在規則的 RETURNING 子句中，OLD 和 NEW 指的是重寫查詢中作為額外範圍表條目新增的偽關係，而不是結果關係中的舊/新行。因此，例如，在支援該檢視上的 UPDATE 查詢的規則中，如果 RETURNING 子句包含 old.sl_name，則將始終返回舊名稱，而不管檢視上的查詢的 RETURNING 子句是否指定了 OLD 或 NEW，這可能會令人困惑。為避免這種混淆，並支援在檢視上的查詢中返回舊值和新值，規則定義中的 RETURNING 子句應引用結果關係中的條目，例如 shoelace_data.sl_name，而不指定 OLD 或 NEW。
現在假設偶爾會有一批鞋帶運到商店，並附帶一個大零件清單。但您不想每次都手動更新 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)
現在將到達的鞋帶移入
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;
並丟棄原始的 INSERT 到 shoelace_ok。此重寫後的查詢再次傳遞給規則系統，應用第二個規則 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，其子查詢條件總共使用了 4 個巢狀/連線檢視，其中一個檢視本身有一個包含檢視的子查詢條件，並且使用了計算出的檢視列，這會被重寫為單個查詢樹，該樹將請求的資料從真實表中刪除。
在現實世界中，可能只有少數情況需要這種構造。但它讓您感到放心，因為它有效。
如果您在文件中看到任何不正確、與您對特定功能的體驗不符或需要進一步說明的內容，請使用 此表單 報告文件問題。