From 5e45211a64149b3c659b90ff2de6fa982a5a93ed Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:17:33 +0200 Subject: Adding upstream version 15.5. Signed-off-by: Daniel Baumann --- doc/src/sgml/ref/create_rule.sgml | 307 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 307 insertions(+) create mode 100644 doc/src/sgml/ref/create_rule.sgml (limited to 'doc/src/sgml/ref/create_rule.sgml') diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml new file mode 100644 index 0000000..76029ab --- /dev/null +++ b/doc/src/sgml/ref/create_rule.sgml @@ -0,0 +1,307 @@ + + + + + CREATE RULE + + + + CREATE RULE + 7 + SQL - Language Statements + + + + CREATE RULE + define a new rewrite rule + + + + +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 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 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 . + + + + Presently, ON SELECT rules can only be attached + to views. (Attaching one to a table converts the table into a view.) + 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. + + + + 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. + + + + 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.) + + + + + A view that is simple enough to be automatically updatable (see ) 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. + + + + Another alternative worth considering is to use INSTEAD OF + triggers (see ) 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 indicates that the commands should be + executed instead of the original command. + + + + + + + + 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. + + + + + + + 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. + + + + 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. + + + + 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; + + + + + 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; + + 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 is a + PostgreSQL language extension, as is the + entire query rewrite system. + + + + + See Also + + + + + + + + -- cgit v1.2.3