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';
通过规则生成。此查询树 chắc chắn sẽ chèn ba mục nhật ký mới. Và điều đó hoàn toàn chính xác.
Tại đây, chúng ta có thể thấy lý do tại sao điều quan trọng là cây truy vấn gốc phải được thực thi cuối cùng. Nếu UPDATE đã được thực thi trước, thì tất cả các hàng đã được đặt thành zero, vì vậy INSERT ghi nhật ký sẽ không tìm thấy bất kỳ hàng nào trong đó 0 <> shoelace_data.sl_avail.
Một cách đơn giản để bảo vệ quan hệ chế độ xem khỏi khả năng nêu trên là ai đó có thể thử chạy INSERT, UPDATE hoặc DELETE trên chế độ xem đó là vứt bỏ các cây truy vấn đó. Vì vậy, chúng ta có thể tạo các quy tắc
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;
Nếu ai đó giờ đây cố gắng thực hiện bất kỳ thao tác nào trong số này trên quan hệ chế độ xem shoe, thì hệ thống quy tắc sẽ áp dụng các quy tắc này. Vì các quy tắc không có hành động và là INSTEAD, nên danh sách các cây truy vấn kết quả sẽ trống và toàn bộ truy vấn sẽ trở thành không cần thiết vì không còn gì để tối ưu hóa hoặc thực thi sau khi hệ thống quy tắc hoàn tất.
Một cách tinh vi hơn để sử dụng hệ thống quy tắc là tạo ra các quy tắc viết lại cây truy vấn thành một cây truy vấn thực hiện đúng thao tác trên các bảng thực. Để thực hiện điều đó trên chế độ xem shoelace, chúng ta tạo các quy tắc sau
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;
Nếu bạn muốn hỗ trợ các truy vấn RETURNING trên chế độ xem, thì bạn cần các quy tắc bao gồm các mệnh đề RETURNING tính các hàng chế độ xem. Điều này thường khá dễ dàng đối với các chế độ xem trên một bảng duy nhất, nhưng hơi tẻ nhạt đối với các chế độ xem liên kết như shoelace. Một ví dụ cho trường hợp chèn là
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);
Lưu ý rằng quy tắc này hỗ trợ cả các truy vấn INSERT và INSERT RETURNING trên chế độ xem — mệnh đề RETURNING sẽ chỉ bị bỏ qua cho INSERT.
Giờ hãy giả sử rằng thỉnh thoảng sẽ có một gói dây giày đến cửa hàng cùng với một danh sách các phụ tùng. Nhưng bạn không muốn cập nhật thủ công chế độ xem shoelace mỗi lần. Thay vào đó, chúng tôi thiết lập hai bảng nhỏ: một bảng nơi bạn có thể chèn các mục từ danh sách phụ tùng và một bảng có một thủ thuật đặc biệt. Các lệnh tạo cho những mục này là
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;
Giờ đây, bạn có thể điền dữ liệu từ danh sách phụ tùng vào bảng shoelace_arrive
SELECT * FROM shoelace_arrive; arr_name | arr_quant ----------+----------- sl3 | 10 sl6 | 20 sl8 | 20 (3 rows)
Xem qua nhanh dữ liệu hiện tại
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)
Bây giờ chuyển giày đã đến vào
INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;
và kiểm tra kết quả
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对一个视图，使用一个子查询限定，该子查询限定总共使用了 4 个嵌套/联接视图，其中一个自身有一个包含视图的子查询限定，其中使用了计算的视图列，被重新写入到一个单独的查询树中，该查询树从一个真实表中删除了请求的数据。
在现实世界中，可能很少有几个情况需要这样的构造。但它会让你感到它的工作原理会比较舒适。