Skip to content

Commit 917bbeb

Browse files
tglsfdctglsfdc
committed
Apply a simple solution to the problem of making INSERT/UPDATE/DELETE
RETURNING play nice with views/rules. To wit, have the rule rewriter rewrite any RETURNING clause found in a rule to produce what the rule's triggering query asked for in its RETURNING clause, in particular drop the RETURNING clause if no RETURNING in the triggering query. This leaves the responsibility for knowing how to produce the view's output columns on the rule author, without requiring any fundamental changes in rule semantics such as adding new rule event types would do. The initial implementation constrains things to ensure that there is exactly one, unconditionally invoked RETURNING clause among the rules for an event --- later we might be able to relax that, but for a post feature freeze fix it seems better to minimize how much invention we do. Per gripe from Jaime Casanova.
1 parent 74924d2 commit 917bbeb

File tree

6 files changed

+496
-92
lines changed

6 files changed

+496
-92
lines changed

‎doc/src/sgml/ref/create_rule.sgml

Lines changed: 24 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.45 2005/01/04 00:39:53 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.46 2006/09/02 17:06:52 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -70,7 +70,9 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS
7070
<literal>ON INSERT</literal>, <literal>ON UPDATE</literal>, and
7171
<literal>ON DELETE</literal> rules (or any subset of those that's
7272
sufficient for your purposes) to replace update actions on the view
73-
with appropriate updates on other tables.
73+
with appropriate updates on other tables. If you want to support
74+
<command>INSERT RETURNING</> and so on, then be sure to put a suitable
75+
<literal>RETURNING</> clause into each of these rules.
7476
</para>
7577

7678
<para>
@@ -87,7 +89,8 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS
8789
understands it will never be called on to update the dummy table.
8890
Then make the conditional rules non-<literal>INSTEAD</literal>; in
8991
the cases where they are applied, they add to the default
90-
<literal>INSTEAD NOTHING</literal> action.
92+
<literal>INSTEAD NOTHING</literal> action. (This method does not
93+
currently work to support <literal>RETURNING</> queries, however.)
9194
</para>
9295
</refsect1>
9396

@@ -201,13 +204,30 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS
201204
be allowed to define a rule on it.
202205
</para>
203206

207+
<para>
208+
In a rule for <literal>INSERT</literal>, <literal>UPDATE</literal>, or
209+
<literal>DELETE</literal> on a view, you can add a <literal>RETURNING</>
210+
clause that emits the view's columns. This clause will be used to compute
211+
the outputs if the rule is triggered by an <command>INSERT RETURNING</>,
212+
<command>UPDATE RETURNING</>, or <command>DELETE RETURNING</> command
213+
respectively. When the rule is triggered by a command without
214+
<literal>RETURNING</>, the rule's <literal>RETURNING</> clause will be
215+
ignored. The current implementation allows only unconditional
216+
<literal>INSTEAD</> rules to contain <literal>RETURNING</>; furthermore
217+
there can be at most one <literal>RETURNING</> clause among all the rules
218+
for the same event. (This ensures that there is only one candidate
219+
<literal>RETURNING</> clause to be used to compute the results.)
220+
<literal>RETURNING</> queries on the view will be rejected if
221+
there is no <literal>RETURNING</> clause in any available rule.
222+
</para>
223+
204224
<para>
205225
It is very important to take care to avoid circular rules. For
206226
example, though each of the following two rule definitions are
207227
accepted by <productname>PostgreSQL</productname>, the
208228
<command>SELECT</command> command would cause
209229
<productname>PostgreSQL</productname> to report an error because
210-
the query cycled too many times:
230+
of recursive expansion of a rule:
211231

212232
<programlisting>
213233
CREATE RULE "_RETURN" AS

‎doc/src/sgml/rules.sgml

Lines changed: 36 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/rules.sgml,v 1.45 2006/04/23 03:39:52 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/rules.sgml,v 1.46 2006/09/02 17:06:52 tgl Exp $ -->
22

33
<chapter id="rules">
44
<title>The Rule System</title>
@@ -873,7 +873,7 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
873873

874874
<listitem>
875875
<para>
876-
They can be <literal>INSTEAD</> or <literal>ALSO</> (default).
876+
They can be <literal>INSTEAD</> or <literal>ALSO</> (the default).
877877
</para>
878878
</listitem>
879879

@@ -920,7 +920,8 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS
920920
Initially the query-tree list is empty.
921921
There can be zero (<literal>NOTHING</> key word), one, or multiple actions.
922922
To simplify, we will look at a rule with one action. This rule
923-
can have a qualification or not and it can be <literal>INSTEAD</> or <literal>ALSO</> (default).
923+
can have a qualification or not and it can be <literal>INSTEAD</> or
924+
<literal>ALSO</> (the default).
924925
</para>
925926

926927
<para>
@@ -932,22 +933,13 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS
932933
</para>
933934

934935
<para>
935-
So we have four cases that produce the following query trees for
936+
So we have three cases that produce the following query trees for
936937
a one-action rule.
937938

938939
<variablelist>
939940
<varlistentry>
940-
<term>No qualification and <literal>ALSO</></term>
941-
<listitem>
942-
<para>
943-
the query tree from the rule action with the original query
944-
tree's qualification added
945-
</para>
946-
</listitem>
947-
</varlistentry>
948-
949-
<varlistentry>
950-
<term>No qualification but <literal>INSTEAD</></term>
941+
<term>No qualification, with either <literal>ALSO</> or
942+
<literal>INSTEAD</></term>
951943
<listitem>
952944
<para>
953945
the query tree from the rule action with the original query
@@ -1283,7 +1275,7 @@ SELECT shoelace_data.sl_name, 0,
12831275
A simple way to protect view relations from the mentioned
12841276
possibility that someone can try to run <command>INSERT</command>,
12851277
<command>UPDATE</command>, or <command>DELETE</command> on them is
1286-
to let those query trees get thrown away. So we create the rules
1278+
to let those query trees get thrown away. So we could create the rules
12871279

12881280
<programlisting>
12891281
CREATE RULE shoe_ins_protect AS ON INSERT TO shoe
@@ -1338,6 +1330,34 @@ CREATE RULE shoelace_del AS ON DELETE TO shoelace
13381330
</programlisting>
13391331
</para>
13401332

1333+
<para>
1334+
If you want to support <literal>RETURNING</> queries on the view,
1335+
you need to make the rules include <literal>RETURNING</> clauses that
1336+
compute the view rows. This is usually pretty trivial for views on a
1337+
single table, but it's a bit tedious for join views such as
1338+
<literal>shoelace</literal>. An example for the insert case is
1339+
1340+
<programlisting>
1341+
CREATE RULE shoelace_ins AS ON INSERT TO shoelace
1342+
DO INSTEAD
1343+
INSERT INTO shoelace_data VALUES (
1344+
NEW.sl_name,
1345+
NEW.sl_avail,
1346+
NEW.sl_color,
1347+
NEW.sl_len,
1348+
NEW.sl_unit
1349+
)
1350+
RETURNING
1351+
shoelace_data.*,
1352+
(SELECT shoelace_data.sl_len * u.un_fact
1353+
FROM unit u WHERE shoelace_data.sl_unit = u.un_name);
1354+
</programlisting>
1355+
1356+
Note that this one rule supports both <command>INSERT</> and
1357+
<command>INSERT RETURNING</> queries on the view &mdash; the
1358+
<literal>RETURNING</> clause is simply ignored for <command>INSERT</>.
1359+
</para>
1360+
13411361
<para>
13421362
Now assume that once in a while, a pack of shoelaces arrives at
13431363
the shop and a big parts list along with it. But you don't want

0 commit comments

Comments
 (0)