{"Entry":{"collection":"sql","key":"create-rule","name":"CREATE RULE","aliases":["createrule"],"metadata":{"aliases":["createrule"],"changed_in":["7.1","7.3","7.4","8.0","9.2","9.4"],"changes":[{"from":"6.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":null,"to":"6.5"},{"from":"6.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage","compatibility"],"removed":["bugs"]},"status":"changed","synopsis":null,"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-createrule.htm","to_file":"sql-createrule.html"},"sections":{"added":[],"changed":["description"],"removed":["usage"]},"status":"changed","synopsis":{"added":["DO [ INSTEAD ] action","where action can be:","NOTHING","|","query","|","( query ; query ... )","|","[ query ; query ... ]"],"removed":["DO [ INSTEAD ] [ action | NOTHING ]"]},"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"7.2"},{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ OR REPLACE ] RULE name AS ON event","TO table [ WHERE condition ]"],"removed":["TO object [ WHERE condition ]","|","[ query ; query ... ]"]},"to":"7.3"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["parameters","notes"],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["DO [ INSTEAD ] { NOTHING | command | ( command ; command ... ) }"],"removed":["DO [ INSTEAD ] action","where action can be:","| query","| ( query ; query ... )"]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }"],"removed":[]},"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"8.1"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"9.0"},{"from":"9.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"9.1"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["TO table_name [ WHERE condition ]"],"removed":["TO table [ WHERE condition ]"]},"to":"9.2"},{"from":"9.2","purpose_changed":false,"renamed":null,"sections":{"added":["see_also"],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"9.3"},{"from":"9.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"changed","synopsis":{"added":["SELECT | INSERT | UPDATE | DELETE"],"removed":[]},"to":"9.4"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"9.5"},{"from":"9.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"9.6"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"12"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"14"},{"from":"15","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"16"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"17"}],"content_hash":"9b2fe499c3a63c4fe4e2801d293b6eed9a9fb5360e3c88d37872c02a3ed5e026","editorial":{},"first_version":"6.4","group":"trigger","imported_at":"2026-09-30T17:43:38.350939+08:00","last_version":"20","name":"CREATE RULE","object":"RULE","position":6004,"present_in":["6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"define a new rewrite rule","purpose_zh":"","related":["alter-rule","drop-rule"],"slug":"create-rule","source_rev":"a709ab85","synopsis":"CREATE [ OR REPLACE ] RULE name AS ON event\nTO table_name [ WHERE condition ]\nDO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }\n\nwhere event can be one of:\n\nSELECT | INSERT | UPDATE | DELETE","verb":"CREATE"}},"Definition":{"Collection":"sql","Key":"create-rule","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"create-rule","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-CREATERULE","file":"sql-createrule.html","lang":"en","name":"CREATE RULE","purpose":"define a new rewrite rule","purpose_zh":"","related":["alter-rule","drop-rule"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE RULE\u003c/code\u003e defines a new rule applying to a specified table or view. \u003ccode class=\"command\"\u003eCREATE OR REPLACE RULE\u003c/code\u003e will either create a new rule, or replace an existing rule of the same name for the same table.\u003c/p\u003e\u003cp\u003eThe \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 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 \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e 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 \u003ca href=\"/docs/18/rules.html\" title=\"Chapter 39. The Rule System\"\u003eChapter 39\u003c/a\u003e.\u003c/p\u003e\u003cp\u003ePresently, \u003ccode class=\"literal\"\u003eON SELECT\u003c/code\u003e rules can only be attached to views. Such a rule must be named \u003ccode class=\"literal\"\u003e\"_RETURN\"\u003c/code\u003e, must be an unconditional \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e rule, and must have an action that consists of a single \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003eCREATE OR REPLACE RULE \"_RETURN\" AS ...\u003c/code\u003e, it's better style to use \u003ccode class=\"literal\"\u003eCREATE OR REPLACE VIEW\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eYou can create the illusion of an updatable view by defining \u003ccode class=\"literal\"\u003eON INSERT\u003c/code\u003e, \u003ccode class=\"literal\"\u003eON UPDATE\u003c/code\u003e, and \u003ccode class=\"literal\"\u003eON DELETE\u003c/code\u003e 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 \u003ccode class=\"command\"\u003eINSERT RETURNING\u003c/code\u003e and so on, then be sure to put a suitable \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause into each of these rules.\u003c/p\u003e\u003cp\u003eThere is a catch if you try to use conditional rules for complex view updates: there \u003cspan class=\"emphasis\"\u003e\u003cem\u003emust\u003c/em\u003e\u003c/span\u003e be an unconditional \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e rule for each action you wish to allow on the view. If the rule is conditional, or is not \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e, 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 \u003ccode class=\"literal\"\u003eDO INSTEAD NOTHING\u003c/code\u003e rule to ensure that the system understands it will never be called on to update the dummy table. Then make the conditional rules non-\u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e; in the cases where they are applied, they add to the default \u003ccode class=\"literal\"\u003eINSTEAD NOTHING\u003c/code\u003e action. (This method does not currently work to support \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e queries, however.)\u003c/p\u003e\u003cdiv class=\"note\"\u003e\u003ch3\u003eNote\u003c/h3\u003e\u003cp\u003eA view that is simple enough to be automatically updatable (see \u003ca href=\"/docs/18/sql-createview.html\" title=\"CREATE VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE VIEW\u003c/span\u003e\u003c/a\u003e) 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.\u003c/p\u003e\u003cp\u003eAnother alternative worth considering is to use \u003ccode class=\"literal\"\u003eINSTEAD OF\u003c/code\u003e triggers (see \u003ca href=\"/docs/18/sql-createtrigger.html\" title=\"CREATE TRIGGER\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TRIGGER\u003c/span\u003e\u003c/a\u003e) in place of rules.\u003c/p\u003e\u003c/div\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe 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.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eevent\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe event is one of \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e, \u003ccode class=\"literal\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e, or \u003ccode class=\"literal\"\u003eDELETE\u003c/code\u003e. Note that an \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e containing an \u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e clause cannot be used on tables that have either \u003ccode class=\"literal\"\u003eINSERT\u003c/code\u003e or \u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e rules. Consider using an updatable view instead.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of the table or view the rule applies to.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAny \u003cacronym\u003eSQL\u003c/acronym\u003e conditional expression (returning \u003ccode class=\"type\"\u003eboolean\u003c/code\u003e). The condition expression cannot refer to any tables except \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e and \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e, and cannot contain aggregate functions.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"option\"\u003eINSTEAD\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e indicates that the commands should be executed \u003cspan class=\"emphasis\"\u003e\u003cem\u003einstead of\u003c/em\u003e\u003c/span\u003e the original command.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"option\"\u003eALSO\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eALSO\u003c/code\u003e indicates that the commands should be executed \u003cspan class=\"emphasis\"\u003e\u003cem\u003ein addition to\u003c/em\u003e\u003c/span\u003e the original command.\u003c/p\u003e\u003cp\u003eIf neither \u003ccode class=\"literal\"\u003eALSO\u003c/code\u003e nor \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e is specified, \u003ccode class=\"literal\"\u003eALSO\u003c/code\u003e is the default.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecommand\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe command or commands that make up the rule action. Valid commands are \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e, \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e, or \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003eWithin \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e and \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecommand\u003c/code\u003e\u003c/em\u003e, the special table names \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e and \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e can be used to refer to values in the referenced table. \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e is valid in \u003ccode class=\"literal\"\u003eON INSERT\u003c/code\u003e and \u003ccode class=\"literal\"\u003eON UPDATE\u003c/code\u003e rules to refer to the new row being inserted or updated. \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e is valid in \u003ccode class=\"literal\"\u003eON UPDATE\u003c/code\u003e and \u003ccode class=\"literal\"\u003eON DELETE\u003c/code\u003e rules to refer to the existing row being updated or deleted.\u003c/p\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eYou must be the owner of a table to create or change rules for it.\u003c/p\u003e\u003cp\u003eIn a rule for \u003ccode class=\"literal\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e, or \u003ccode class=\"literal\"\u003eDELETE\u003c/code\u003e on a view, you can add a \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause that emits the view's columns. This clause will be used to compute the outputs if the rule is triggered by an \u003ccode class=\"command\"\u003eINSERT RETURNING\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE RETURNING\u003c/code\u003e, or \u003ccode class=\"command\"\u003eDELETE RETURNING\u003c/code\u003e command respectively. When the rule is triggered by a command without \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e, the rule's \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause will be ignored. The current implementation allows only unconditional \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e rules to contain \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e; furthermore there can be at most one \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause among all the rules for the same event. (This ensures that there is only one candidate \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause to be used to compute the results.) \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e queries on the view will be rejected if there is no \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause in any available rule.\u003c/p\u003e\u003cp\u003eIt is very important to take care to avoid circular rules. For example, though each of the following two rule definitions are accepted by \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e, the \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e command would cause \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e to report an error because of recursive expansion of a rule:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE RULE \"_RETURN\" AS\n    ON SELECT TO t1\n    DO INSTEAD\n        SELECT * FROM t2;\n\nCREATE RULE \"_RETURN\" AS\n    ON SELECT TO t2\n    DO INSTEAD\n        SELECT * FROM t1;\n\nSELECT * FROM t1;\n\u003c/pre\u003e\u003cp\u003ePresently, if a rule action contains a \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e command, the \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e command will be executed unconditionally, that is, the \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e will be issued even if there are not any rows that the rule should apply to. For example, in:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;\n\nUPDATE mytable SET name = 'foo' WHERE id = 42;\n\u003c/pre\u003e\u003cp\u003eone \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e event will be sent during the \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, whether or not there are any rows that match the condition \u003ccode class=\"literal\"\u003eid = 42\u003c/code\u003e. This is an implementation restriction that might be fixed in future releases.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE RULE\u003c/code\u003e is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e language extension, as is the entire query rewrite system.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-rule/?v=18\" title=\"ALTER RULE\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER RULE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-rule/?v=18\" title=\"DROP RULE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP RULE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"CREATE [ OR REPLACE ] RULE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e AS ON \u003cem class=\"replaceable\"\u003e\u003ccode\u003eevent\u003c/code\u003e\u003c/em\u003e\n    TO \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ WHERE \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e ]\n    DO [ ALSO | INSTEAD ] { NOTHING | \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecommand\u003c/code\u003e\u003c/em\u003e | ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecommand\u003c/code\u003e\u003c/em\u003e ; \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecommand\u003c/code\u003e\u003c/em\u003e ... ) }\n\n\u003cspan class=\"phrase\"\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003eevent\u003c/code\u003e\u003c/em\u003e can be one of:\u003c/span\u003e\n\n    SELECT | INSERT | UPDATE | DELETE","synopsis_text":"CREATE [ OR REPLACE ] RULE name AS ON event\nTO table_name [ WHERE condition ]\nDO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }\n\nwhere event can be one of:\n\nSELECT | INSERT | UPDATE | DELETE"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"create-rule","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"CREATE RULE","Summary":"定义一条新的重写规则","BodyHTML":"\u003cpre\u003eCREATE [ OR REPLACE ] RULE name AS ON event\nTO table_name [ WHERE condition ]\nDO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }\n\n其中 event 可以是以下之一：\n\nSELECT | INSERT | UPDATE | DELETE\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE RULE\u003c/code\u003e定义一条应用于指定表或视图的新规则。\u003ccode\u003eCREATE OR REPLACE RULE\u003c/code\u003e将创建一条新规则，或者替换同一个表上同名的现有规则。\u003c/p\u003e\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e规则系统允许定义在数据库表上执行插入、更新或删除时的替代动作。粗略地说，当在某个给定表上执行某个命令时，一条规则会导致额外的命令被执行。另一种情况是，\u003ccode\u003eINSTEAD\u003c/code\u003e 规则可以用另一个命令替换给定命令，或者让某个命令完全不执行。规则也用于实现 SQL 视图。必须认识到，规则实际上是一种命令转换机制，或者说是命令宏。这种转换发生在命令开始执行之前。如果你真正需要的是对每个物理行独立触发的操作，那么你很可能应该使用触发器，而不是规则。有关规则系统的更多信息见\u003ca href=\"/docs/18/rules.html\" rel=\"nofollow\"\u003e第 39 章\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e目前，\u003ccode\u003eON SELECT\u003c/code\u003e规则只能附加到视图上。这样的规则必须命名为\u003ccode\u003e\u0026#34;_RETURN\u0026#34;\u003c/code\u003e，必须是无条件的\u003ccode\u003eINSTEAD\u003c/code\u003e规则，并且必须有一个由单个\u003ccode\u003eSELECT\u003c/code\u003e命令组成的动作。该命令定义了视图中可见的内容。（视图本身基本上是一个没有存储的虚拟表。）最好把这样的规则视为实现细节。尽管可以通过\u003ccode\u003eCREATE OR REPLACE RULE \u0026#34;_RETURN\u0026#34; AS ...\u003c/code\u003e重新定义视图，但风格上更好的做法是使用 \u003ccode\u003eCREATE OR REPLACE VIEW\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e可以通过定义\u003ccode\u003eON INSERT\u003c/code\u003e、\u003ccode\u003eON UPDATE\u003c/code\u003e 和\u003ccode\u003eON DELETE\u003c/code\u003e规则（或其中足以满足你需求的任意子集），来营造出可更新视图的效果，从而将视图上的更新动作替换为对其他表的适当更新。如果想支持\u003ccode\u003eINSERT RETURNING\u003c/code\u003e等功能，那么务必在这些规则中的每一条里都放入合适的\u003ccode\u003eRETURNING\u003c/code\u003e子句。\u003c/p\u003e\u003cp\u003e如果你尝试对复杂视图更新使用有条件规则，这里有一个陷阱：对于你希望允许在该视图上执行的每一种动作，\u003cspan\u003e\u003cem\u003e必须\u003c/em\u003e\u003c/span\u003e都有一条无条件的 \u003ccode\u003eINSTEAD\u003c/code\u003e规则。如果该规则是有条件的，或者不是 \u003ccode\u003eINSTEAD\u003c/code\u003e规则，那么系统仍会拒绝执行该更新动作，因为它认为在某些情况下最终可能还是会尝试在该视图的虚拟表上执行该动作。如果你想用有条件规则处理所有有用的情况，可以加上一条无条件的 \u003ccode\u003eDO INSTEAD NOTHING\u003c/code\u003e规则，以确保系统明白它永远不会被要求去更新这个虚拟表。然后，把这些有条件规则改成非 \u003ccode\u003eINSTEAD\u003c/code\u003e规则；在它们适用的情况下，它们会附加在默认的 \u003ccode\u003eINSTEAD NOTHING\u003c/code\u003e动作之上。（不过，这种方法当前仍无法支持 \u003ccode\u003eRETURNING\u003c/code\u003e查询。）\u003c/p\u003e\u003cdiv\u003e\u003ch3\u003e注意\u003c/h3\u003e\u003cp\u003e足够简单、可自动更新的视图（见\u003ca href=\"/docs/18/sql-createview.html\" title=\"CREATE VIEW\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE VIEW\u003c/span\u003e\u003c/a\u003e）不需要依靠用户创建的规则来实现更新。虽然你仍然可以显式创建规则，但自动更新转换通常比显式规则性能更好。\u003c/p\u003e\u003cp\u003e另一种值得考虑的办法是使用\u003ccode\u003eINSTEAD OF\u003c/code\u003e触发器（见\u003ca href=\"/docs/18/sql-createtrigger.html\" title=\"CREATE TRIGGER\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TRIGGER\u003c/span\u003e\u003c/a\u003e）代替规则。\u003c/p\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的规则的名称。它必须与同一个表上任何其他规则的名称相区分。同一个表上同一种事件类型的多条规则会按名称的字母顺序应用。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eevent\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e事件是\u003ccode\u003eSELECT\u003c/code\u003e、\u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e或者 \u003ccode\u003eDELETE\u003c/code\u003e之一。注意，包含\u003ccode\u003eON CONFLICT\u003c/code\u003e子句的\u003ccode\u003eINSERT\u003c/code\u003e 不能用于带有\u003ccode\u003eINSERT\u003c/code\u003e或 \u003ccode\u003eUPDATE\u003c/code\u003e规则的表。请考虑改用可更新的视图。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e规则适用的表或者视图的名称（可以是模式限定的）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e任意的\u003cacronym\u003eSQL\u003c/acronym\u003e条件表达式（返回 \u003ccode\u003eboolean\u003c/code\u003e）。条件表达式不能引用\u003ccode\u003eNEW\u003c/code\u003e和 \u003ccode\u003eOLD\u003c/code\u003e之外的任何表，并且不能包含聚合函数。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eINSTEAD\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode\u003eINSTEAD\u003c/code\u003e表示这些命令应当\u003cspan\u003e\u003cem\u003e替代\u003c/em\u003e\u003c/span\u003e原始命令执行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eALSO\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode\u003eALSO\u003c/code\u003e表示这些命令应当在原始命令\u003cspan\u003e\u003cem\u003e之外\u003c/em\u003e\u003c/span\u003e执行。\u003c/p\u003e\u003cp\u003e如果既未指定\u003ccode\u003eALSO\u003c/code\u003e，也未指定 \u003ccode\u003eINSTEAD\u003c/code\u003e，则默认值为\u003ccode\u003eALSO\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecommand\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e组成规则动作的命令。可用的命令有\u003ccode\u003eSELECT\u003c/code\u003e、\u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e、\u003ccode\u003eDELETE\u003c/code\u003e或者\u003ccode\u003eNOTIFY\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e在\u003cem\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e和 \u003cem\u003e\u003ccode\u003ecommand\u003c/code\u003e\u003c/em\u003e中，可以使用特殊表名 \u003ccode\u003eNEW\u003c/code\u003e和\u003ccode\u003eOLD\u003c/code\u003e来引用目标表中的值。在\u003ccode\u003eON INSERT\u003c/code\u003e和\u003ccode\u003eON UPDATE\u003c/code\u003e规则中，\u003ccode\u003eNEW\u003c/code\u003e可用于引用待插入或待更新的新行。在 \u003ccode\u003eON UPDATE\u003c/code\u003e和\u003ccode\u003eON DELETE\u003c/code\u003e规则中，\u003ccode\u003eOLD\u003c/code\u003e可用于引用将被更新或删除的现有行。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e要在表上创建或者修改规则，必须是表的拥有者。\u003c/p\u003e\u003cp\u003e在视图上的\u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e或 \u003ccode\u003eDELETE\u003c/code\u003e规则中，可以添加一个输出视图各列的 \u003ccode\u003eRETURNING\u003c/code\u003e子句。如果该规则分别由 \u003ccode\u003eINSERT RETURNING\u003c/code\u003e、\u003ccode\u003eUPDATE RETURNING\u003c/code\u003e 或\u003ccode\u003eDELETE RETURNING\u003c/code\u003e命令触发，那么这个子句将用于计算输出。当规则由不带\u003ccode\u003eRETURNING\u003c/code\u003e的命令触发时，该规则的 \u003ccode\u003eRETURNING\u003c/code\u003e子句会被忽略。当前实现只允许无条件的 \u003ccode\u003eINSTEAD\u003c/code\u003e规则包含\u003ccode\u003eRETURNING\u003c/code\u003e；此外，对于同一事件的所有规则，总共最多只能有一个\u003ccode\u003eRETURNING\u003c/code\u003e子句。（这可以确保只有一个候选\u003ccode\u003eRETURNING\u003c/code\u003e子句可用于计算结果。）如果任何可用规则中都没有 \u003ccode\u003eRETURNING\u003c/code\u003e子句，那么视图上的\u003ccode\u003eRETURNING\u003c/code\u003e查询将被拒绝。\u003c/p\u003e\u003cp\u003e避免循环规则非常重要。例如，尽管下面的两条规则定义都被 \u003cspan\u003ePostgreSQL\u003c/span\u003e所接受，\u003ccode\u003eSELECT\u003c/code\u003e命令将导致 \u003cspan\u003ePostgreSQL\u003c/span\u003e报告错误，因为规则会发生递归展开：\u003c/p\u003e\u003cpre\u003eCREATE RULE \u0026#34;_RETURN\u0026#34; AS\n    ON SELECT TO t1\n    DO INSTEAD\n        SELECT * FROM t2;\n\nCREATE RULE \u0026#34;_RETURN\u0026#34; AS\n    ON SELECT TO t2\n    DO INSTEAD\n        SELECT * FROM t1;\n\nSELECT * FROM t1;\n\u003c/pre\u003e\u003cp\u003e当前，如果一个规则动作包含一个\u003ccode\u003eNOTIFY\u003c/code\u003e命令，该\u003ccode\u003eNOTIFY\u003c/code\u003e命令将被无条件执行，也就是说，即使没有任何应当应用该规则的行，也会发出\u003ccode\u003eNOTIFY\u003c/code\u003e。例如：\u003c/p\u003e\u003cpre\u003eCREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;\n\nUPDATE mytable SET name = \u0026#39;foo\u0026#39; WHERE id = 42;\n\u003c/pre\u003e\u003cp\u003e在\u003ccode\u003eUPDATE\u003c/code\u003e期间会发送一个\u003ccode\u003eNOTIFY\u003c/code\u003e事件，无论是否存在匹配条件\u003ccode\u003eid = 42\u003c/code\u003e的行。这是一个实现限制，未来版本中可能会修复。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE RULE\u003c/code\u003e是一种 \u003cspan\u003ePostgreSQL\u003c/span\u003e语言扩展，整个查询重写系统也是如此。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/alter-rule/?v=18\" title=\"ALTER RULE\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER RULE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-rule/?v=18\" title=\"DROP RULE\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP RULE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"198bf3b06bd5e34c9489f85de9b2299a3bf5edecd2afd74cc0e0bfa166986573","Payload":{"purpose_zh":"定义一条新的重写规则","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE RULE\u003c/code\u003e定义一条应用于指定表或视图的新规则。\u003ccode class=\"command\"\u003eCREATE OR REPLACE RULE\u003c/code\u003e将创建一条新规则，或者替换同一个表上同名的现有规则。\u003c/p\u003e\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e规则系统允许定义在数据库表上执行插入、更新或删除时的替代动作。粗略地说，当在某个给定表上执行某个命令时，一条规则会导致额外的命令被执行。另一种情况是，\u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e 规则可以用另一个命令替换给定命令，或者让某个命令完全不执行。规则也用于实现 SQL 视图。必须认识到，规则实际上是一种命令转换机制，或者说是命令宏。这种转换发生在命令开始执行之前。如果你真正需要的是对每个物理行独立触发的操作，那么你很可能应该使用触发器，而不是规则。有关规则系统的更多信息见\u003ca href=\"/docs/18/rules.html\" title=\"第 39 章 规则系统\"\u003e第 39 章\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e目前，\u003ccode class=\"literal\"\u003eON SELECT\u003c/code\u003e规则只能附加到视图上。这样的规则必须命名为\u003ccode class=\"literal\"\u003e\"_RETURN\"\u003c/code\u003e，必须是无条件的\u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e规则，并且必须有一个由单个\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e命令组成的动作。该命令定义了视图中可见的内容。（视图本身基本上是一个没有存储的虚拟表。）最好把这样的规则视为实现细节。尽管可以通过\u003ccode class=\"literal\"\u003eCREATE OR REPLACE RULE \"_RETURN\" AS ...\u003c/code\u003e重新定义视图，但风格上更好的做法是使用 \u003ccode class=\"literal\"\u003eCREATE OR REPLACE VIEW\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e可以通过定义\u003ccode class=\"literal\"\u003eON INSERT\u003c/code\u003e、\u003ccode class=\"literal\"\u003eON UPDATE\u003c/code\u003e 和\u003ccode class=\"literal\"\u003eON DELETE\u003c/code\u003e规则（或其中足以满足你需求的任意子集），来营造出可更新视图的效果，从而将视图上的更新动作替换为对其他表的适当更新。如果想支持\u003ccode class=\"command\"\u003eINSERT RETURNING\u003c/code\u003e等功能，那么务必在这些规则中的每一条里都放入合适的\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e子句。\u003c/p\u003e\u003cp\u003e如果你尝试对复杂视图更新使用有条件规则，这里有一个陷阱：对于你希望允许在该视图上执行的每一种动作，\u003cspan class=\"emphasis\"\u003e\u003cem\u003e必须\u003c/em\u003e\u003c/span\u003e都有一条无条件的 \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e规则。如果该规则是有条件的，或者不是 \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e规则，那么系统仍会拒绝执行该更新动作，因为它认为在某些情况下最终可能还是会尝试在该视图的虚拟表上执行该动作。如果你想用有条件规则处理所有有用的情况，可以加上一条无条件的 \u003ccode class=\"literal\"\u003eDO INSTEAD NOTHING\u003c/code\u003e规则，以确保系统明白它永远不会被要求去更新这个虚拟表。然后，把这些有条件规则改成非 \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e规则；在它们适用的情况下，它们会附加在默认的 \u003ccode class=\"literal\"\u003eINSTEAD NOTHING\u003c/code\u003e动作之上。（不过，这种方法当前仍无法支持 \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e查询。）\u003c/p\u003e\u003cdiv class=\"note\"\u003e\u003ch3\u003e注意\u003c/h3\u003e\u003cp\u003e足够简单、可自动更新的视图（见\u003ca href=\"/docs/18/sql-createview.html\" title=\"CREATE VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE VIEW\u003c/span\u003e\u003c/a\u003e）不需要依靠用户创建的规则来实现更新。虽然你仍然可以显式创建规则，但自动更新转换通常比显式规则性能更好。\u003c/p\u003e\u003cp\u003e另一种值得考虑的办法是使用\u003ccode class=\"literal\"\u003eINSTEAD OF\u003c/code\u003e触发器（见\u003ca href=\"/docs/18/sql-createtrigger.html\" title=\"CREATE TRIGGER\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TRIGGER\u003c/span\u003e\u003c/a\u003e）代替规则。\u003c/p\u003e\u003c/div\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的规则的名称。它必须与同一个表上任何其他规则的名称相区分。同一个表上同一种事件类型的多条规则会按名称的字母顺序应用。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eevent\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e事件是\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e、\u003ccode class=\"literal\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e或者 \u003ccode class=\"literal\"\u003eDELETE\u003c/code\u003e之一。注意，包含\u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e子句的\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e 不能用于带有\u003ccode class=\"literal\"\u003eINSERT\u003c/code\u003e或 \u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e规则的表。请考虑改用可更新的视图。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e规则适用的表或者视图的名称（可以是模式限定的）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e任意的\u003cacronym\u003eSQL\u003c/acronym\u003e条件表达式（返回 \u003ccode class=\"type\"\u003eboolean\u003c/code\u003e）。条件表达式不能引用\u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e和 \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e之外的任何表，并且不能包含聚合函数。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"option\"\u003eINSTEAD\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e表示这些命令应当\u003cspan class=\"emphasis\"\u003e\u003cem\u003e替代\u003c/em\u003e\u003c/span\u003e原始命令执行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"option\"\u003eALSO\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eALSO\u003c/code\u003e表示这些命令应当在原始命令\u003cspan class=\"emphasis\"\u003e\u003cem\u003e之外\u003c/em\u003e\u003c/span\u003e执行。\u003c/p\u003e\u003cp\u003e如果既未指定\u003ccode class=\"literal\"\u003eALSO\u003c/code\u003e，也未指定 \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e，则默认值为\u003ccode class=\"literal\"\u003eALSO\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecommand\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e组成规则动作的命令。可用的命令有\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e、\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e、\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e或者\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e在\u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e和 \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecommand\u003c/code\u003e\u003c/em\u003e中，可以使用特殊表名 \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e和\u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e来引用目标表中的值。在\u003ccode class=\"literal\"\u003eON INSERT\u003c/code\u003e和\u003ccode class=\"literal\"\u003eON UPDATE\u003c/code\u003e规则中，\u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e可用于引用待插入或待更新的新行。在 \u003ccode class=\"literal\"\u003eON UPDATE\u003c/code\u003e和\u003ccode class=\"literal\"\u003eON DELETE\u003c/code\u003e规则中，\u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e可用于引用将被更新或删除的现有行。\u003c/p\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e要在表上创建或者修改规则，必须是表的拥有者。\u003c/p\u003e\u003cp\u003e在视图上的\u003ccode class=\"literal\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e或 \u003ccode class=\"literal\"\u003eDELETE\u003c/code\u003e规则中，可以添加一个输出视图各列的 \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e子句。如果该规则分别由 \u003ccode class=\"command\"\u003eINSERT RETURNING\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE RETURNING\u003c/code\u003e 或\u003ccode class=\"command\"\u003eDELETE RETURNING\u003c/code\u003e命令触发，那么这个子句将用于计算输出。当规则由不带\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e的命令触发时，该规则的 \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e子句会被忽略。当前实现只允许无条件的 \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e规则包含\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e；此外，对于同一事件的所有规则，总共最多只能有一个\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e子句。（这可以确保只有一个候选\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e子句可用于计算结果。）如果任何可用规则中都没有 \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e子句，那么视图上的\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e查询将被拒绝。\u003c/p\u003e\u003cp\u003e避免循环规则非常重要。例如，尽管下面的两条规则定义都被 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e所接受，\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e命令将导致 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e报告错误，因为规则会发生递归展开：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE RULE \"_RETURN\" AS\n    ON SELECT TO t1\n    DO INSTEAD\n        SELECT * FROM t2;\n\nCREATE RULE \"_RETURN\" AS\n    ON SELECT TO t2\n    DO INSTEAD\n        SELECT * FROM t1;\n\nSELECT * FROM t1;\n\u003c/pre\u003e\u003cp\u003e当前，如果一个规则动作包含一个\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e命令，该\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e命令将被无条件执行，也就是说，即使没有任何应当应用该规则的行，也会发出\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e。例如：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;\n\nUPDATE mytable SET name = 'foo' WHERE id = 42;\n\u003c/pre\u003e\u003cp\u003e在\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e期间会发送一个\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e事件，无论是否存在匹配条件\u003ccode class=\"literal\"\u003eid = 42\u003c/code\u003e的行。这是一个实现限制，未来版本中可能会修复。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE RULE\u003c/code\u003e是一种 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e语言扩展，整个查询重写系统也是如此。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-rule/?v=18\" title=\"ALTER RULE\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER RULE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-rule/?v=18\" title=\"DROP RULE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP RULE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"CREATE [ OR REPLACE ] RULE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e AS ON \u003cem class=\"replaceable\"\u003e\u003ccode\u003eevent\u003c/code\u003e\u003c/em\u003e\n    TO \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ WHERE \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e ]\n    DO [ ALSO | INSTEAD ] { NOTHING | \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecommand\u003c/code\u003e\u003c/em\u003e | ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecommand\u003c/code\u003e\u003c/em\u003e ; \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecommand\u003c/code\u003e\u003c/em\u003e ... ) }\n\n\u003cspan class=\"phrase\"\u003e其中 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eevent\u003c/code\u003e\u003c/em\u003e 可以是以下之一：\u003c/span\u003e\n\n    SELECT | INSERT | UPDATE | DELETE","synopsis_text":"CREATE [ OR REPLACE ] RULE name AS ON event\nTO table_name [ WHERE condition ]\nDO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }\n\n其中 event 可以是以下之一：\n\nSELECT | INSERT | UPDATE | DELETE"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}