All,
From rules-update.sgml:
Take the section from 35.3.2 beginning:
"If you want to support RETURNING queries on the view, you need to make the
rules include RETURNING clauses that compute the view rows."
Remove it. Add a new subsection, titled:
UPDATE RULES and the RETURNING CLAUSE
Add text:
=================
If you want to use INSERT ... RETURNING or UPDATE ... RETURNING with your DO
INSTEAD RULE on a table or view, you need to take an additional step. To the
unconditional, final DO INSTEAD rule, you need to add a RETURNING clause,
which requires the following:
1. All columns of the originating table/view must be mentioned in the RULE's
RETURNING clause;
2. The RETURNING clause will refer only to columns in the target table of the
DO INSTEAD, not the originating table/view.
3. All data types in the RETURNING clause must match the data types of the
table or view columns in order of its definition.
A simple example:
CREATE TABLE test1 ( a serial, b int, c text );
CREATE TABLE test2 ( c serial, d int, e text );
CREATE RULE test1_insert AS ON INSERT INTO test1
DO INSTEAD INSERT INTO test2 ( d, e ) VALUES ( NEW.b, NEW.c )
RETURNING c, d, e;
Note that you could also return d, 5, e or c, 7, 'joe', or anything you wanted
as long as the data types match. The calling process will only get the
RETURNING columns for which they ask.
An example from the "shoelaces" INSERT case above:
=================
Continue with the Shoelaces example and the final paragraph about RETURNING,
finishing with:
"...the RETURNING clause is simply ignored for INSERT."
--
Josh Berkus
PostgreSQL @ Sun
San Francisco