CREATE RULE 中文man页面-51CTO.COM

NAME

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

SYNOPSIS

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

DESCRIPTION 描述

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

PostgreSQL规则系统允许我们在从数据库或表中更新，插入或删除东西时定义一个其它的动作来执行。简单说，规则就是当我们在指定的表上执行指定的动作的时候，导致一些额外的动作被执行。另外，规则可以用另外一个命令取代某个特定的命令，或者令命令完全不被执行。规则还用于实现表视图。我们要明白的是规则实际上只是一个命令转换机制，或者说命令宏。这种转换发生在命令开始执行之前。如果你实际上想要一个为每个物理行独立发生的操作，那么你可能还是要用一个触发器，而不是规则。有关规则的更多信息可以在 ``The Rule System'' 找到。

目前，ON SELECT 规则必须是无条件的 INSTEAD 规则并且必须有一个由一条 SELECT 查询组成的动作。因此，一条 ON SELECT 规则有效地把对象表转成视图，它的可见内容是规则的 SELECT 查询返回的记录而不是存储在表中的内容（如果有的话）。我们认为写一条 CREATE VIEW 命令比创建一个表然后定义一条 ON SELECT 规则在上面的风格要好。

你可以创建一个可以更新的视图的幻觉，方法是在视图上定义 ON INSERT，ON UPDATE，和 ON DELETE 规则（或者满足你需要的任何上述规则的子集），用合适的对其它表的更新替换在视图上更新的动作。

如果你想在视图更新上使用条件规则，那么这里就有一个补充：对你希望在视图上允许的每个动作，你都必须有一个无条件的 INSTEAD 规则。如果规则是有条件的，或者它不是 INSTEAD，那么系统仍将拒绝执行更新动作的企图，因为它认为它最终会在某种程度上在虚拟表上执行动作。如果你想处理条件规则上的所由有用的情况，那也可以；只需要增加一个无条件的 DO INSTEAD NOTHING 规则确保系统明白它将决不会被调用来更新虚拟表就可以了。然后把条件规则做成非 INSTEAD；在这种情况下，如果它们被触发，那么它们就增加到缺省的 INSTEAD NOTHING 动作中。

PARAMETERS 参数

name: 创建的规则名。它必须在同一个表上的所有规则的名字中唯一。同一个表上的同一个事件类型的规则是按照字母顺序运行的。
event: 事件是 SELECT， UPDATE，DELETE 或 INSERT 之一。
table: 规则施用的表或者视图的名字（可以有模式修饰）。
condition: 任意 SQL 条件表达式（返回 boolean）。条件表达式除了引用 NEW 和 OLD 之外不能引用任何表，并且不能有聚集函数。
command: 组成规则动作的命令。有效的命令是 SELECT，INSERT， UPDATE，DELETE，或 NOTIFY 语句之一。

在 condition 和 command 里，特殊表名字 NEW 和 OLD 可以用于指向被引用表里的数值 new 在 ON INSERT 和 ON UPDATE 规则里可以指向被插入或更新的新行。 OLD 在 ON UPDATE，和 ON DELETE 规则里可以指向现存的被更新，或者删除的行。

NOTES 注意

为了在表上定义规则，你必须有 RULE 权限。

有一件很重要的事情是要避免循环规则。比如，尽管下面两条规则定义都是 PostgreSQL 可以接受的，但一条 SELECT 命令会导致 PostgreSQL 报告一条错误信息，因为该查询循环了太多次：

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 也会被发出。比如，在

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

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

里，一个 NOTIFY 事件将在 UPDATE 的时候发出，不管是否有某行的 id = 42。这是一个实现的限制，将来的版本应该修补这个毛病。

#p#

NAME

CREATE RULE - define a new rewrite rule

SYNOPSIS

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

DESCRIPTION

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.

The PostgreSQL rule system allows one to define an alternate 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, a rule can replace a given command by another, or cause a command not to be executed at all. Rules are used to implement table 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 commands 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 the chapter called ``The Rule System'' in the documentation.

Presently, ON SELECT rules must be unconditional INSTEAD rules and must have actions that consist of a single SELECT command. Thus, an ON SELECT rule effectively turns the table into a view, whose visible contents are the rows returned by the rule's SELECT command rather than whatever had been stored in the table (if anything). It is considered better style to write a CREATE VIEW command than to create a real table and define an ON SELECT rule for it.

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.

There is a catch if you try to use conditional rules for 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, you can; just 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 not INSTEAD; in the cases where they are applied, they add to the default INSTEAD NOTHING action.

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 even is one of SELECT, INSERT, UPDATE, or DELETE.
table: The name (optionally schema-qualified) of the table or view the rule applies to.
condition: Any SQL conditional expression (returning boolean). The condition expression may not refer to any tables except NEW and OLD, and may not contain aggregate functions.
command: The command or commands that make up the rule action. Valid commands are SELECT, INSERT, UPDATE, DELETE, or NOTIFY.

Within condition and command, the special table names NEW and OLD may 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 have the privilege RULE on a table to be allowed to define a rule on it.

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 the query cycled too many times:

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;

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 NOTIFY mytable;

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

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