NAME

CREATE RULE - 定義一個(gè)新的重寫規(guī)則

SYNOPSIS

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

DESCRIPTION 描述

CREATE RULE 定義一個(gè)適用于特定表或者視圖的新規(guī)則。 CREATE OR REPLACE RULE 要么是創(chuàng)建一個(gè)新規(guī)則， 要么是用一個(gè)同表上的同名規(guī)則替換現(xiàn)有規(guī)則。

PostgreSQL規(guī)則系統(tǒng)允許我們在從數(shù)據(jù)庫或表中更新， 插入或刪除東西時(shí)定義一個(gè)其它的動(dòng)作來執(zhí)行。 簡單說，規(guī)則就是當(dāng)我們在指定的表上執(zhí)行指定的動(dòng)作的時(shí)候，導(dǎo)致一些額外的動(dòng)作被執(zhí)行。 另外，規(guī)則可以用另外一個(gè)命令取代某個(gè)特定的命令，或者令命令完全不被執(zhí)行。 規(guī)則還用于實(shí)現(xiàn)表視圖。我們要明白的是規(guī)則實(shí)際上只是一個(gè)命令轉(zhuǎn)換機(jī)制，或者說命令宏。 這種轉(zhuǎn)換發(fā)生在命令開始執(zhí)行之前。如果你實(shí)際上想要一個(gè)為每個(gè)物理行獨(dú)立發(fā)生的操作， 那么你可能還是要用一個(gè)觸發(fā)器，而不是規(guī)則。有關(guān)規(guī)則的更多信息可以在 ``The Rule System'' 找到。

 目前，ON SELECT 規(guī)則必須是無條件的 INSTEAD 規(guī)則并且必須有一個(gè)由一條 SELECT 查詢組成的動(dòng)作。 因此，一條 ON SELECT 規(guī)則有效地把對象表轉(zhuǎn)成視圖， 它的可見內(nèi)容是規(guī)則的 SELECT 查詢返回的記錄而不是存儲(chǔ)在表中的內(nèi)容（如果有的話）。 我們認(rèn)為寫一條 CREATE VIEW 命令比創(chuàng)建一個(gè)表然后定義一條 ON SELECT 規(guī)則在上面的風(fēng)格要好。

 你可以創(chuàng)建一個(gè)可以更新的視圖的幻覺， 方法是在視圖上定義 ON INSERT，ON UPDATE，和 ON DELETE  規(guī)則（或者滿足你需要的任何上述規(guī)則的子集），用合適的對其它表的更新替換在視圖上更新的動(dòng)作。

 如果你想在視圖更新上使用條件規(guī)則，那么這里就有一個(gè)補(bǔ)充： 對你希望在視圖上允許的每個(gè)動(dòng)作，你都必須有一個(gè)無條件的 INSTEAD 規(guī)則。 如果規(guī)則是有條件的，或者它不是 INSTEAD， 那么系統(tǒng)仍將拒絕執(zhí)行更新動(dòng)作的企圖，因?yàn)樗J(rèn)為它最終會(huì)在某種程度上在虛擬表上執(zhí)行動(dòng)作。 如果你想處理?xiàng)l件規(guī)則上的所由有用的情況，那也可以；只需要增加一個(gè)無條件的 DO INSTEAD NOTHING 規(guī)則確保系統(tǒng)明白它將決不會(huì)被調(diào)用來更新虛擬表就可以了。 然后把條件規(guī)則做成非 INSTEAD；在這種情況下，如果它們被觸發(fā)，那么它們就增加到缺省的 INSTEAD NOTHING  動(dòng)作中。  

PARAMETERS 參數(shù)

name

 創(chuàng)建的規(guī)則名。它必須在同一個(gè)表上的所有規(guī)則的名字中唯一。 同一個(gè)表上的同一個(gè)事件類型的規(guī)則是按照字母順序運(yùn)行的。

event

 事件是 SELECT， UPDATE，DELETE  或 INSERT 之一。

table

 規(guī)則施用的表或者視圖的名字（可以有模式修飾）。

condition

 任意 SQL 條件表達(dá)式（返回 boolean）。 條件表達(dá)式除了引用 NEW 和 OLD 之外不能引用任何表，并且不能有聚集函數(shù)。

command

 組成規(guī)則動(dòng)作的命令。有效的命令是 SELECT，INSERT， UPDATE，DELETE，或 NOTIFY 語句之一。

 在 condition  和 command 里， 特殊表名字 NEW 和 OLD 可以用于指向被引用表里的數(shù)值 new 在 ON INSERT 和 ON UPDATE 規(guī)則里可以指向被插入或更新的新行。 OLD 在 ON UPDATE，和 ON DELETE 規(guī)則里可以指向現(xiàn)存的被更新，或者刪除的行。

NOTES 注意

 為了在表上定義規(guī)則，你必須有 RULE 權(quán)限。

 有一件很重要的事情是要避免循環(huán)規(guī)則。 比如，盡管下面兩條規(guī)則定義都是 PostgreSQL 可以接受的， 但一條 SELECT 命令會(huì)導(dǎo)致 PostgreSQL 報(bào)告一條錯(cuò)誤信息，因?yàn)樵摬樵冄h(huán)了太多次：

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;

 目前，如果一個(gè)規(guī)則包含一個(gè) NOTIFY 查詢，那么該 NOTIFY 將被無條件執(zhí)行 --- 也就是說，如果規(guī)則不施加到任何行上頭， 該 NOTIFY 也會(huì)被發(fā)出。比如，在

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

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

 里，一個(gè) NOTIFY 事件將在 UPDATE 的時(shí)候發(fā)出，不管是否有某行的 id = 42。這是一個(gè)實(shí)現(xiàn)的限制，將來的版本應(yīng)該修補(bǔ)這個(gè)毛病。  

#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.