diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ref/create_rule.sgml | 28 | ||||
| -rw-r--r-- | doc/src/sgml/rules.sgml | 52 |
2 files changed, 60 insertions, 20 deletions
diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index 1ab861ddf17..9380ab5a184 100644 --- a/doc/src/sgml/ref/create_rule.sgml +++ b/doc/src/sgml/ref/create_rule.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.45 2005/01/04 00:39:53 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.46 2006/09/02 17:06:52 tgl Exp $ PostgreSQL documentation --> @@ -70,7 +70,9 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS <literal>ON INSERT</literal>, <literal>ON UPDATE</literal>, and <literal>ON DELETE</literal> 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. + with appropriate updates on other tables. If you want to support + <command>INSERT RETURNING</> and so on, then be sure to put a suitable + <literal>RETURNING</> clause into each of these rules. </para> <para> @@ -87,7 +89,8 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS understands it will never be called on to update the dummy table. Then make the conditional rules non-<literal>INSTEAD</literal>; in the cases where they are applied, they add to the default - <literal>INSTEAD NOTHING</literal> action. + <literal>INSTEAD NOTHING</literal> action. (This method does not + currently work to support <literal>RETURNING</> queries, however.) </para> </refsect1> @@ -202,12 +205,29 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS </para> <para> + In a rule for <literal>INSERT</literal>, <literal>UPDATE</literal>, or + <literal>DELETE</literal> on a view, you can add a <literal>RETURNING</> + clause that emits the view's columns. This clause will be used to compute + the outputs if the rule is triggered by an <command>INSERT RETURNING</>, + <command>UPDATE RETURNING</>, or <command>DELETE RETURNING</> command + respectively. When the rule is triggered by a command without + <literal>RETURNING</>, the rule's <literal>RETURNING</> clause will be + ignored. The current implementation allows only unconditional + <literal>INSTEAD</> rules to contain <literal>RETURNING</>; furthermore + there can be at most one <literal>RETURNING</> clause among all the rules + for the same event. (This ensures that there is only one candidate + <literal>RETURNING</> clause to be used to compute the results.) + <literal>RETURNING</> queries on the view will be rejected if + there is no <literal>RETURNING</> clause in any available rule. + </para> + + <para> It is very important to take care to avoid circular rules. For example, though each of the following two rule definitions are accepted by <productname>PostgreSQL</productname>, the <command>SELECT</command> command would cause <productname>PostgreSQL</productname> to report an error because - the query cycled too many times: + of recursive expansion of a rule: <programlisting> CREATE RULE "_RETURN" AS diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index ad5a2d4bfea..84cf3bce622 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/rules.sgml,v 1.45 2006/04/23 03:39:52 momjian Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/rules.sgml,v 1.46 2006/09/02 17:06:52 tgl Exp $ --> <chapter id="rules"> <title>The Rule System</title> @@ -873,7 +873,7 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; <listitem> <para> - They can be <literal>INSTEAD</> or <literal>ALSO</> (default). + They can be <literal>INSTEAD</> or <literal>ALSO</> (the default). </para> </listitem> @@ -920,7 +920,8 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS Initially the query-tree list is empty. There can be zero (<literal>NOTHING</> key word), one, or multiple actions. To simplify, we will look at a rule with one action. This rule - can have a qualification or not and it can be <literal>INSTEAD</> or <literal>ALSO</> (default). + can have a qualification or not and it can be <literal>INSTEAD</> or + <literal>ALSO</> (the default). </para> <para> @@ -932,22 +933,13 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS </para> <para> - So we have four cases that produce the following query trees for + So we have three cases that produce the following query trees for a one-action rule. <variablelist> <varlistentry> - <term>No qualification and <literal>ALSO</></term> - <listitem> - <para> - the query tree from the rule action with the original query - tree's qualification added - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term>No qualification but <literal>INSTEAD</></term> + <term>No qualification, with either <literal>ALSO</> or + <literal>INSTEAD</></term> <listitem> <para> the query tree from the rule action with the original query @@ -1283,7 +1275,7 @@ SELECT shoelace_data.sl_name, 0, A simple way to protect view relations from the mentioned possibility that someone can try to run <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command> on them is - to let those query trees get thrown away. So we create the rules + to let those query trees get thrown away. So we could create the rules <programlisting> CREATE RULE shoe_ins_protect AS ON INSERT TO shoe @@ -1339,6 +1331,34 @@ CREATE RULE shoelace_del AS ON DELETE TO shoelace </para> <para> + If you want to support <literal>RETURNING</> queries on the view, + you need to make the rules include <literal>RETURNING</> clauses that + compute the view rows. This is usually pretty trivial for views on a + single table, but it's a bit tedious for join views such as + <literal>shoelace</literal>. An example for the insert case is + +<programlisting> +CREATE RULE shoelace_ins AS ON INSERT TO shoelace + DO INSTEAD + INSERT INTO shoelace_data VALUES ( + NEW.sl_name, + NEW.sl_avail, + NEW.sl_color, + NEW.sl_len, + NEW.sl_unit + ) + RETURNING + shoelace_data.*, + (SELECT shoelace_data.sl_len * u.un_fact + FROM unit u WHERE shoelace_data.sl_unit = u.un_name); +</programlisting> + + Note that this one rule supports both <command>INSERT</> and + <command>INSERT RETURNING</> queries on the view — the + <literal>RETURNING</> clause is simply ignored for <command>INSERT</>. + </para> + + <para> Now assume that once in a while, a pack of shoelaces arrives at the shop and a big parts list along with it. But you don't want to manually update the <literal>shoelace</literal> view every |