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... ) }
在下文中，更新規則表示定義在 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 系統目錄的動作中找到的查詢樹僅是範本。 由於它們可以引用 NEW 和 OLD 的範圍表條目，因此必須先進行一些替換才能使用它們。 對於任何對 NEW 的引用，都會在原始查詢的目標列表中搜尋相應的條目。 如果找到，該條目的表達式將替換該引用。 否則，NEW 的含義與 OLD 相同（對於 UPDATE），或者被空值替換（對於 INSERT）。 對 OLD 的任何引用都會被替換為對作為結果關係的範圍表條目的引用。
系統完成應用更新規則後，它會將視窗規則應用於產生的查詢樹。 視窗不能插入新的更新動作，因此無需將更新規則應用於視窗重寫的輸出。
假設我們要追蹤 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 規則，因此規則系統必須傳回兩個查詢樹：修改後的規則動作和原始查詢樹。 在步驟 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，我們也會輸出原始查詢樹。 簡而言之，規則系統的輸出是兩個查詢樹的列表，它們對應於以下陳述式
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 查詢— RETURNING 子句對於 INSERT 只是被忽略。
現在假設偶爾會有一批鞋帶送到店裡，並附帶一份大型零件清單。但您不希望每次都手動更新 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 個巢狀/聯結視窗的子查詢限定，其中一個視窗本身具有包含視窗的子查詢限定，並且使用計算的視窗欄位，這一切都會被重寫為一個單一的查詢樹，從實際表格中刪除請求的資料。
在現實世界中可能只有少數情況需要這樣的建構。但這讓您感到安心，它可以正常運作。
如果您在文件中發現任何不正確、與特定功能的經驗不符或需要進一步澄清的地方，請使用此表單回報文件問題。