Postgresql 中文操作指南

CREATE RULE

CREATE RULE — 定义一个新的重写规则

CREATE RULE — define a new rewrite rule

Synopsis

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

where event can be one of:

    SELECT | INSERT | UPDATE | DELETE

Description

CREATE RULE 定义适用于指定表或视图的新规则。 CREATE OR REPLACE RULE 要么创建一个新规则，要么替换同一张表的同名现有规则。

CREATE RULE defines a new rule applying to a specified table or view. CREATE OR REPLACE RULE will either create a new rule, or replace an existing rule of the same name for the same table.

PostgreSQL 规则系统允许人们定义在数据库表中插入、更新或删除时执行的替代操作。粗略来说，一个规则会导致在给定表上执行给定命令时执行附加命令。或者， INSTEAD 规则可以将给定命令替换为另一个命令，或者导致根本不执行命令。规则还用于实现 SQL 视图。重要的是要认识到，规则实际上是一种命令转换机制或命令宏。转换在命令执行开始之前发生。如果您实际上希望对每个物理行独立触发操作，则可能希望使用触发器而不是规则。有关规则系统的更多信息，请参阅 Chapter 41 。

The PostgreSQL rule system allows one to define an alternative action to be performed on insertions, updates, or deletions in database tables. Roughly speaking, a rule causes additional commands to be executed when a given command on a given table is executed. Alternatively, an INSTEAD rule can replace a given command by another, or cause a command not to be executed at all. Rules are used to implement SQL views as well. It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before the execution of the command starts. If you actually want an operation that fires independently for each physical row, you probably want to use a trigger, not a rule. More information about the rules system is in Chapter 41.

目前， ON SELECT 规则只能附加到视图。此类规则必须命名为 "_RETURN" ，必须是无条件的 INSTEAD 规则，并且必须具有由单个 SELECT 命令组成的一个操作。此命令定义视图的可见内容。（视图本身基本上是一个没有存储的虚拟表。）最好将此类规则视为一个实现细节。虽然可以通过 CREATE OR REPLACE RULE "_RETURN" AS …​ 重新定义视图，但最好使用 CREATE OR REPLACE VIEW 。

Presently, ON SELECT rules can only be attached to views. Such a rule must be named "_RETURN", must be an unconditional INSTEAD rule, and must have an action that consists of a single SELECT command. This command defines the visible contents of the view. (The view itself is basically a dummy table with no storage.) It’s best to regard such a rule as an implementation detail. While a view can be redefined via CREATE OR REPLACE RULE "_RETURN" AS …​, it’s better style to use CREATE OR REPLACE VIEW.

您可以通过定义 ON INSERT 、 ON UPDATE 和 ON DELETE 规则（或足以满足您目的的任何子集）来创建可更新视图的假象，以用其他表上的适当更新替换对视图的更新操作。如果您想支持 INSERT RETURNING 等，则务必在每个此类规则中放入一个适当的 RETURNING 子句。

You can create the illusion of an updatable view by defining ON INSERT, ON UPDATE, and ON DELETE rules (or any subset of those that’s sufficient for your purposes) to replace update actions on the view with appropriate updates on other tables. If you want to support INSERT RETURNING and so on, then be sure to put a suitable RETURNING clause into each of these rules.

如果您尝试对复杂视图更新使用条件规则，那么有个需要注意的地方： must 为您希望在视图上允许的每个操作都必须有一个无条件 INSTEAD 规则。如果该规则是条件的或不是 INSTEAD ，则系统仍会拒绝尝试执行更新操作，因为它认为在某些情况下它最终可能会尝试对视图的虚拟表执行操作。如果您希望在条件规则中处理所有有用的情况，请添加一个无条件 DO INSTEAD NOTHING 规则，以确保系统了解到它永远不会被调用来更新虚拟表。然后使条件规则非 INSTEAD ；在应用它们的案例中，它们会添加到默认 INSTEAD NOTHING 操作。（但是，此方法当前不适用于支持 RETURNING 查询。）

There is a catch if you try to use conditional rules for complex view updates: there must be an unconditional INSTEAD rule for each action you wish to allow on the view. If the rule is conditional, or is not INSTEAD, then the system will still reject attempts to perform the update action, because it thinks it might end up trying to perform the action on the dummy table of the view in some cases. If you want to handle all the useful cases in conditional rules, add an unconditional DO INSTEAD NOTHING rule to ensure that the system understands it will never be called on to update the dummy table. Then make the conditional rules non-INSTEAD; in the cases where they are applied, they add to the default INSTEAD NOTHING action. (This method does not currently work to support RETURNING queries, however.)

Note

视图如果足够简单，可以自动更新（参见 CREATE VIEW ），则不需要用户创建的规则来实现可更新性。虽然你无论如何都可以创建显式规则，不过自动更新转换通常会优于显式规则。

A view that is simple enough to be automatically updatable (see CREATE VIEW) does not require a user-created rule in order to be updatable. While you can create an explicit rule anyway, the automatic update transformation will generally outperform an explicit rule.

另一个值得考虑的备选方案是用 INSTEAD OF 触发器（参见 CREATE TRIGGER ）代替规则。

Another alternative worth considering is to use INSTEAD OF triggers (see CREATE TRIGGER) in place of rules.

Parameters

  • name

    • The name of a rule to create. This must be distinct from the name of any other rule for the same table. Multiple rules on the same table and same event type are applied in alphabetical name order.

  • event

    • The event is one of SELECT, INSERT, UPDATE, or DELETE. Note that an INSERT containing an ON CONFLICT clause cannot be used on tables that have either INSERT or UPDATE rules. Consider using an updatable view instead.

  • table_name

    • The name (optionally schema-qualified) of the table or view the rule applies to.

  • condition

    • Any SQL conditional expression (returning boolean). The condition expression cannot refer to any tables except NEW and OLD, and cannot contain aggregate functions.

  • INSTEAD

    • INSTEAD indicates that the commands should be executed instead of the original command.

  • ALSO

    • ALSO indicates that the commands should be executed in addition to the original command.

    • If neither ALSO nor INSTEAD is specified, ALSO is the default.

  • command

    • The command or commands that make up the rule action. Valid commands are SELECT, INSERT, UPDATE, DELETE, or NOTIFY.

在 condition 和 command 内，可以使用特殊表名 NEW 和 OLD 引用被引用表中的值。 NEW 在 ON INSERT 和 ON UPDATE 规则中是有效的，用于引用要插入或更新的新行。 OLD 在 ON UPDATE 和 ON DELETE 规则中是有效的，用于引用要更新或删除的现有行。

Within condition and command, the special table names NEW and OLD can be used to refer to values in the referenced table. NEW is valid in ON INSERT and ON UPDATE rules to refer to the new row being inserted or updated. OLD is valid in ON UPDATE and ON DELETE rules to refer to the existing row being updated or deleted.

Notes

你必须是表的拥有者才能创建或更改其规则。

You must be the owner of a table to create or change rules for it.

在视图上的 INSERT 、 UPDATE 或 DELETE 的规则中，你可以添加 RETURNING 子句，该子句发出视图的列。如果 INSERT RETURNING 、 UPDATE RETURNING 或 DELETE RETURNING 命令分别触发规则，这个子句将用于计算输出。当通过没有 RETURNING 的命令触发该规则时，规则的 RETURNING 子句将被忽略。当前实现只允许无条件 INSTEAD 规则包含 RETURNING ；此外，同一个事件的所有规则中最多可以有一个 RETURNING 子句。（这确保了只有一个候选 RETURNING 子句用于计算结果。）如果没有 RETURNING 子句存在于任何可用规则中，在视图上的 RETURNING 查询将被拒绝。

In a rule for INSERT, UPDATE, or DELETE on a view, you can add a RETURNING clause that emits the view’s columns. This clause will be used to compute the outputs if the rule is triggered by an INSERT RETURNING, UPDATE RETURNING, or DELETE RETURNING command respectively. When the rule is triggered by a command without RETURNING, the rule’s RETURNING clause will be ignored. The current implementation allows only unconditional INSTEAD rules to contain RETURNING; furthermore there can be at most one RETURNING clause among all the rules for the same event. (This ensures that there is only one candidate RETURNING clause to be used to compute the results.) RETURNING queries on the view will be rejected if there is no RETURNING clause in any available rule.

非常重要的是要小心避免循环规则。例如，虽然 PostgreSQL 接受以下两个规则定义中的每一个，但是 SELECT 命令会导致 PostgreSQL 报告错误，因为规则的递归扩展：

It is very important to take care to avoid circular rules. For example, though each of the following two rule definitions are accepted by PostgreSQL, the SELECT command would cause PostgreSQL to report an error because of recursive expansion of a rule:

CREATE RULE "_RETURN" AS
    ON SELECT TO t1
    DO INSTEAD
        SELECT * FROM t2;

CREATE RULE "_RETURN" AS
    ON SELECT TO t2
    DO INSTEAD
        SELECT * FROM t1;

SELECT * FROM t1;

目前，如果规则动作包含 NOTIFY 命令，那么 NOTIFY 命令将无条件执行，即，即使没有规则应该应用到的行， NOTIFY 也会发出。例如，在：

Presently, if a rule action contains a NOTIFY command, the NOTIFY command will be executed unconditionally, that is, the NOTIFY will be issued even if there are not any rows that the rule should apply to. For example, in:

CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;

UPDATE mytable SET name = 'foo' WHERE id = 42;

一个 NOTIFY 事件将在 UPDATE 期间发送，无论是否有没有与条件 id = 42 匹配的行。这是一个将在未来版本中修复的实现限制。

one NOTIFY event will be sent during the UPDATE, whether or not there are any rows that match the condition id = 42. This is an implementation restriction that might be fixed in future releases.

Compatibility

CREATE RULE 是 PostgreSQL 语言扩展，就像整个查询重写系统一样。

CREATE RULE is a PostgreSQL language extension, as is the entire query rewrite system.

See Also

ALTER RULE 、 DROP RULE

ALTER RULE, DROP RULE