Postgresql 中文操作指南

41.4. Rules on INSERT, UPDATE, and DELETE #

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

Rules that are defined on INSERT, UPDATE, and DELETE are significantly different from the view rules described in the previous sections. First, their CREATE RULE command allows more:

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

Second, they don’t modify the query tree in place. Instead they create zero or more new query trees and can throw away the original one.

Caution

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

In many cases, tasks that could be performed by rules on INSERT/UPDATE/DELETE are better done with triggers. Triggers are notationally a bit more complicated, but their semantics are much simpler to understand. Rules tend to have surprising results when the original query contains volatile functions: volatile functions may get executed more times than expected in the process of carrying out the rules.

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

Also, there are some cases that are not supported by these types of rules at all, notably including WITH clauses in the original query and multiple-assignment sub-SELECT_s in the _SET list of UPDATE queries. This is because copying these constructs into a rule query would result in multiple evaluations of the sub-query, contrary to the express intent of the query’s author.

41.4.1. How Update Rules Work #

记住语法：

Keep the syntax:

CREATE [ OR REPLACE ] RULE name AS ON event
    TO table [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

在以下内容中，update rules 表示在 INSERT、UPDATE 或 DELETE 中定义的规则。

in mind. In the following, update rules means rules that are defined on INSERT, UPDATE, or DELETE.

当查询树的结果关系和命令类型等于 CREATE RULE 命令中给出的对象和事件时，更新规则将由规则系统应用。对于更新规则，规则系统将创建查询树列表。最初查询树列表为空。可以是零个（NOTHING 关键字）、一个或多个动作。为了简化，我们将查看一条具有一个动作的规则。此规则可以有限定条件，也可以没有限定条件，并且可以是 INSTEAD 或 ALSO（默认值）。

Update rules get applied by the rule system when the result relation and the command type of a query tree are equal to the object and event given in the CREATE RULE command. For update rules, the rule system creates a list of query trees. Initially the query-tree list is empty. There can be zero (NOTHING key word), one, or multiple actions. To simplify, we will look at a rule with one action. This rule can have a qualification or not and it can be INSTEAD or ALSO (the default).

什么是规则限定条件？它是一种限制，用于说明何时执行规则的动作，何时不执行。此限定条件只能引用伪关系 NEW 和/或 OLD，基本上表示作为对象给出的关系（但具有特殊含义）。

What is a rule qualification? It is a restriction that tells when the actions of the rule should be done and when not. This qualification can only reference the pseudorelations NEW and/or OLD, which basically represent the relation that was given as object (but with a special meaning).

因此，对于一个动作规则，我们有三种情况会产生以下查询树。

So we have three cases that produce the following query trees for a one-action rule.

  • No qualification, with either ALSO or INSTEAD

    • the query tree from the rule action with the original query tree’s qualification added

  • Qualification given and ALSO

    • the query tree from the rule action with the rule qualification and the original query tree’s qualification added

  • Qualification given and INSTEAD

    • the query tree from the rule action with the rule qualification and the original query tree’s qualification; and the original query tree with the negated rule qualification added

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

Finally, if the rule is ALSO, the unchanged original query tree is added to the list. Since only qualified INSTEAD rules already add the original query tree, we end up with either one or two output query trees for a rule with one action.

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

For ON INSERT rules, the original query (if not suppressed by INSTEAD) is done before any actions added by rules. This allows the actions to see the inserted row(s). But for ON UPDATE and ON DELETE rules, the original query is done after the actions added by rules. This ensures that the actions can see the to-be-updated or to-be-deleted rows; otherwise, the actions might do nothing because they find no rows matching their qualifications.

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

The query trees generated from rule actions are thrown into the rewrite system again, and maybe more rules get applied resulting in additional or fewer query trees. So a rule’s actions must have either a different command type or a different result relation than the rule itself is on, otherwise this recursive process will end up in an infinite loop. (Recursive expansion of a rule will be detected and reported as an error.)

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

The query trees found in the actions of the pg_rewrite system catalog are only templates. Since they can reference the range-table entries for NEW and OLD, some substitutions have to be made before they can be used. For any reference to NEW, the target list of the original query is searched for a corresponding entry. If found, that entry’s expression replaces the reference. Otherwise, NEW means the same as OLD (for an UPDATE) or is replaced by a null value (for an INSERT). Any reference to OLD is replaced by a reference to the range-table entry that is the result relation.

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

After the system is done applying update rules, it applies view rules to the produced query tree(s). Views cannot insert new update actions so there is no need to apply update rules to the output of view rewriting.

41.4.1.1. A First Rule Step by Step #

假设我们希望跟踪 shoelace_data 关系中 sl_avail 列的更改。因此，我们设置了一个日志表和一个在对 shoelace_data 执行 UPDATE 时有条件写入日志条目的规则。

Say we want to trace changes to the sl_avail column in the shoelace_data relation. So we set up a log table and a rule that conditionally writes a log entry when an UPDATE is performed on shoelace_data.

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
                                );

现在有人执行：

Now someone does:

UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';

并且我们查看日志表：

and we look at the log table:

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)

那是我们所预期的。在后台发生的事情如下。解析器创建了查询树：

That’s what we expected. What happened in the background is the following. The parser created the query tree:

UPDATE shoelace_data SET sl_avail = 6
  FROM shoelace_data shoelace_data
 WHERE shoelace_data.sl_name = 'sl7';

有一个 log_shoelace 规则，它是 ON UPDATE，其规则限定表达式：

There is a rule log_shoelace that is ON UPDATE with the rule qualification expression:

NEW.sl_avail <> OLD.sl_avail

和操作：

and the action:

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 命令的查询树中的变量引用。）

(This looks a little strange since you cannot normally write INSERT …​ VALUES …​ FROM. The FROM clause here is just to indicate that there are range-table entries in the query tree for new and old. These are needed so that they can be referenced by variables in the INSERT command’s query tree.)

该规则是限定的 ALSO 规则，因此规则系统必须返回两个查询树：修改后的规则操作和原始查询树。在步骤 1 中，将原始查询的范围表并入到规则的操作查询树。这导致：

The rule is a qualified ALSO rule, so the rule system has to return two query trees: the modified rule action and the original query tree. In step 1, the range table of the original query is incorporated into the rule’s action query tree. This results in:

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 发生更改的行：

In step 2, the rule qualification is added to it, so the result set is restricted to rows where sl_avail changes:

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 支持相同的功能。）

(This looks even stranger, since INSERT …​ VALUES doesn’t have a WHERE clause either, but the planner and executor will have no difficulty with it. They need to support this same functionality anyway for INSERT …​ SELECT.)

在步骤 3 中，添加了原始查询树的限定，进一步将结果集限制为仅原始查询本来会触及到的行：

In step 3, the original query tree’s qualification is added, restricting the result set further to only the rows that would have been touched by the original query:

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 的引用：

Step 4 replaces references to NEW by the target list entries from the original query tree or by the matching variable references from the result relation:

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 引用更改为结果关系引用：

Step 5 changes OLD references into result relation references:

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，因此我们还会输出原始查询树。简而言之，规则系统输出的是两个查询树的列表，它们对应于以下语句：

That’s it. Since the rule is ALSO, we also output the original query tree. In short, the output from the rule system is a list of two query trees that correspond to these statements:

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';

它们将按此顺序执行，而这正是规则的意义所在。

These are executed in this order, and that is exactly what the rule was meant to do.

替换和添加的限定符确保即使原始查询是，比如：

The substitutions and the added qualifications ensure that, if the original query would be, say:

UPDATE shoelace_data SET sl_color = 'green'
 WHERE sl_name = 'sl7';

也不会写入日志条目。在该情况下，原始查询树不包含 sl_avail 的目标列表条目，所以 NEW.sl_avail 将被 shoelace_data.sl_avail 替换。因此，规则生成的附加命令是：

no log entry would get written. In that case, the original query tree does not contain a target list entry for sl_avail, so NEW.sl_avail will get replaced by shoelace_data.sl_avail. Thus, the extra command generated by the rule is:

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';

并且该限定符永远不会为真。

and that qualification will never be true.

如果原始查询修改多行，这也会起作用。因此，如果某人发布以下命令：

It will also work if the original query modifies multiple rows. So if someone issued the command:

UPDATE shoelace_data SET sl_avail = 0
 WHERE sl_color = 'black';

实际上更新了四行（sl1、sl2、sl3 和 sl4）。但 sl3 已包含 sl_avail = 0。在该情况下，原始查询树的限定符有所不同，导致规则生成附加查询树：

four rows in fact get updated (sl1, sl2, sl3, and sl4). But sl3 already has sl_avail = 0. In this case, the original query trees qualification is different and that results in the extra query tree:

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';

生成。该查询树肯定会插入三个新日志条目。而这是绝对正确的。

being generated by the rule. This query tree will surely insert three new log entries. And that’s absolutely correct.

这里我们可以看到，执行原始查询树至关重要。如果 UPDATE 先执行，所有行都会已设为零，因此日志记录 INSERT 找不到任何包含 0 <> shoelace_data.sl_avail 的行。

Here we can see why it is important that the original query tree is executed last. If the UPDATE had been executed first, all the rows would have already been set to zero, so the logging INSERT would not find any row where 0 <> shoelace_data.sl_avail.

41.4.2. Cooperation with Views #

保护视图关系以防止有人尝试对它们运行 INSERT、UPDATE 或 DELETE 的一个简单方法是丢弃这些查询树。因此，我们可创建以下规则：

A simple way to protect view relations from the mentioned possibility that someone can try to run INSERT, UPDATE, or DELETE on them is to let those query trees get thrown away. So we could create the rules:

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，因此查询树结果列表将为空，并且整个查询将变成空，因为在规则系统执行后没有剩余内容可以优化或执行。

If someone now tries to do any of these operations on the view relation shoe, the rule system will apply these rules. Since the rules have no actions and are INSTEAD, the resulting list of query trees will be empty and the whole query will become nothing because there is nothing left to be optimized or executed after the rule system is done with it.

使用规则系统的更复杂方法是创建规则，将查询树重写为对实际表执行正确操作的一个查询树。若要对 shoelace 视图执行此操作，我们创建以下规则：

A more sophisticated way to use the rule system is to create rules that rewrite the query tree into one that does the right operation on the real tables. To do that on the shoelace view, we create the following rules:

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 这样的联接视图来说有点繁琐。插入用例的一个示例是：

If you want to support RETURNING queries on the view, you need to make the rules include RETURNING clauses that compute the view rows. This is usually pretty trivial for views on a single table, but it’s a bit tedious for join views such as shoelace. An example for the insert case is:

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 根本不会被忽略。

Note that this one rule supports both INSERT and INSERT RETURNING queries on the view — the RETURNING clause is simply ignored for INSERT.

现在假设有一批鞋带到达商店，并且还有一张包含大量部件的大部件清单。但你不想每次都手动更新 shoelace 视图。相反，我们设置了两个小表：一个用于插入部件清单中的物品，另一个则带有特殊技巧。这些表的创建命令是：

Now assume that once in a while, a pack of shoelaces arrives at the shop and a big parts list along with it. But you don’t want to manually update the shoelace view every time. Instead we set up two little tables: one where you can insert the items from the part list, and one with a special trick. The creation commands for these are:

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 表：

Now you can fill the table shoelace_arrive with the data from the parts list:

SELECT * FROM shoelace_arrive;

 arr_name | arr_quant
----------+-----------
 sl3      |        10
 sl6      |        20
 sl8      |        20
(3 rows)

快速查看当前数据：

Take a quick look at the current data:

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)

现在将到达的鞋带移入：

Now move the arrived shoelaces in:

INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;

并检查结果：

and check the results:

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 条到上述结果，还有很长的路要走。有关查询树转换的描述将是本章的最后一部分。首先是解析器的输出：

It’s a long way from the one INSERT …​ SELECT to these results. And the description of the query-tree transformation will be the last in this chapter. First, there is the parser’s output:

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 规则，得到如下结果：

Now the first rule shoelace_ok_ins is applied and turns this into:

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 将生成以下内容：

and throws away the original INSERT on shoelace_ok. This rewritten query is passed to the rule system again, and the second applied rule shoelace_upd produces:

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 规则，然后得到：

Again it’s an INSTEAD rule and the previous query tree is trashed. Note that this query still uses the view shoelace. But the rule system isn’t finished with this step, so it continues and applies the _RETURN rule on it, and we get:

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，从而生成额外的查询树：

Finally, the rule log_shoelace gets applied, producing the extra query tree:

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;

在此之后，规则系统规则用尽，并返回生成的查询树。

After that the rule system runs out of rules and returns the generated query trees.

因此，我们最终得到两条查询树，它们等效于 SQL 语句：

So we end up with two final query trees that are equivalent to the SQL statements:

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;

结果是：源自一个关系的数据插入到另一个关系，更改为对第三个关系的更新，更改为更新第四个关系外加记录第五个关系中的最终更新，这些内容已简化为两条查询。

The result is that data coming from one relation inserted into another, changed into updates on a third, changed into updating a fourth plus logging that final update in a fifth gets reduced into two queries.

有一个不太美观的小细节。通过查看两条查询，事实证明 shoelace_data 关系在区域表中出现过两次，而它绝对可以简化为一次。计划程序无法处理它，因此规则系统的输出 INSERT 的执行计划为

There is a little detail that’s a bit ugly. Looking at the two queries, it turns out that the shoelace_data relation appears twice in the range table where it could definitely be reduced to one. The planner does not handle it and so the execution plan for the rule systems output of the INSERT will be

Nested Loop
  ->  Merge Join
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on s
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on shoelace_arrive
  ->  Seq Scan on shoelace_data

同时省略额外的区域表条目将导致

while omitting the extra range table entry would result in a

Merge Join
  ->  Seq Scan
        ->  Sort
              ->  Seq Scan on s
  ->  Seq Scan
        ->  Sort
              ->  Seq Scan on shoelace_arrive

它们在记录表中生成完全相同的条目。因此，规则系统对表 shoelace_data 导致了一次完全没有必要的额外扫描。在 UPDATE 中又执行了一次相同的冗余扫描。但是，要让这一切成为可能确实是一项艰巨的工作。

which produces exactly the same entries in the log table. Thus, the rule system caused one extra scan on the table shoelace_data that is absolutely not necessary. And the same redundant scan is done once more in the UPDATE. But it was a really hard job to make that all possible at all.

现在，对 PostgreSQL 规则系统及其功能进行最终演示。假设您向您的数据库中添加了一些颜色非常特殊的鞋带：

Now we make a final demonstration of the PostgreSQL rule system and its power. Say you add some shoelaces with extraordinary colors to your database:

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 条目在颜色上不适合任何鞋子。为此，视图如下：

We would like to make a view to check which shoelace entries do not fit any shoe in color. The view for this is:

CREATE VIEW shoelace_mismatch AS
    SELECT * FROM shoelace WHERE NOT EXISTS
        (SELECT shoename FROM shoe WHERE slcolor = sl_color);

它的输出是：

Its output is:

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 设置一点难度，我们不会直接删除它。而是创建另一个视图：

Now we want to set it up so that mismatching shoelaces that are not in stock are deleted from the database. To make it a little harder for PostgreSQL, we don’t delete it directly. Instead we create one more view:

CREATE VIEW shoelace_can_delete AS
    SELECT * FROM shoelace_mismatch WHERE sl_avail = 0;

并以这种方式进行：

and do it this way:

DELETE FROM shoelace WHERE EXISTS
    (SELECT * FROM shoelace_can_delete
             WHERE sl_name = shoelace.sl_name);

结果是：

The results are:

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 个嵌套/连接视图，其中一个本身带有包含视图的子查询限定符，并且使用了计算的视图列，将其重写为一个将请求数据从真实表中删除的单一查询树。

A DELETE on a view, with a subquery qualification that in total uses 4 nesting/joined views, where one of them itself has a subquery qualification containing a view and where calculated view columns are used, gets rewritten into one single query tree that deletes the requested data from a real table.

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

There are probably only a few situations out in the real world where such a construct is necessary. But it makes you feel comfortable that it works.