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 个嵌套/连接视图，其中一个视图本身有一个包含视图的子查询条件，并且使用了计算出的视图列，这会被重写为单个查询树，该树将请求的数据从真实表中删除。
在现实世界中，可能只有少数情况需要这种构造。但它让您感到放心，因为它有效。