Postgresql 中文操作指南

41.4. Rules on INSERT, UPDATE, and DELETE #

在 INSERT、UPDATE 和 DELETE 中定义的规则与前面各节中描述的视图规则有很大不同。首先，它们的 CREATE RULE 命令允许更多：

其次，它们不会就地修改查询树。相反，它们创建零个或一个以上的新查询树，并且可以丢弃原始查询树。

Caution

在很多情况下，INSERT/UPDATE/DELETE 上规则可以执行的任务可以通过触发器更好地完成。触发器的符号表示稍微复杂一些，但其语义更容易理解。当原始查询包含不稳定函数时，规则往往会产生令人惊讶的结果：不稳定函数可能在执行规则过程中执行的次数超出了预期。

另外，有些情况根本不受这些类型的规则支持，尤其包括原始查询中的 WITH 子句和 UPDATE 查询的多次赋值子 SELECT_s in the _SET 列表。这是因为将这些构造复制到规则查询中会导致对子查询进行多次评估，这与查询作者明确的意图相反。

41.4.1. How Update Rules Work #

记住语法：

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
- 带原始查询树的限定符的规则操作的查询树
Qualification given and ALSO
- 带规则限定符和原始查询树的限定符的规则操作的查询树
Qualification given and INSTEAD
- 带规则限定符和原始查询树的限定符的规则操作的查询树；以及添加了否定规则限定符的原始查询树

最后，如果规则是 ALSO，不变的原始查询树将添加到列表中。由于只有限定 INSTEAD 规则已经添加了原始查询树，最终可以为具有一个操作的规则得到一个或两个输出查询树。

对于 ON INSERT 规则，在规则添加的任何操作之前完成原始查询（如果没有被 INSTEAD 抑制）。这允许操作查看插入的行。但对于 ON UPDATE 和 ON DELETE 规则，在规则添加的操作之后完成原始查询。这确保了操作可以查看待更新或待删除的行；否则，操作可能什么都不会做，因为它们找不到与 their 限定匹配的行。

从规则操作中生成的查询树再次被丢进重写系统中，也许更多规则会得到应用从而产生附加或更少的查询树。因此规则的操作必须具有与规则本身不同的命令类型或不同的结果关系，否则此递归过程将最终形成一个无限循环。（规则的递归展开将被检测到并报告为一个错误。）

在 pg_rewrite 系统目录的操作中发现的查询树仅是模板。由于它们可以引用 NEW 和 OLD 的范围表条目，有些替代必须在它们可用于之前完成。对于对 NEW 的任何引用，会搜索原始查询的目标列表以寻找一个相应条目。如果找到，该条目的表达式将替换该引用。否则，NEW 意味着与 OLD 相同（对于一个 UPDATE）或替换为一个空值（对于一个 INSERT）。对 OLD 的任何引用均替换为对结果关系的范围表条目的引用。

在系统完成应用更新规则之后，它对生成查询树应用视图规则。视图无法插入新的更新操作，因此无需对视图重写的输出应用更新规则。

41.4.1.1. A First Rule Step by Step #

假设我们希望跟踪 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 的行。

41.4.2. Cooperation with Views #

保护视图关系以防止有人尝试对它们运行 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 个嵌套/连接视图，其中一个本身带有包含视图的子查询限定符，并且使用了计算的视图列，将其重写为一个将请求数据从真实表中删除的单一查询树。

在现实世界中，可能只有少数情况需要这种结构。但这会让你感觉使用它是舒适的。