From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL @ Sun
To: pgsql-docs@postgresql.org
Subject: Improve doc: RETURNING and RULES
Date: Wed, 12 Sep 2007 11:44:57 -0700
User-Agent: KMail/1.8.2
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200709121144.57176.josh@agliodbs.com>

All,

=46rom 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=
=20
rules include RETURNING clauses that compute the view rows."

Remove it.  Add a new subsection, titled:

UPDATE RULES and the RETURNING CLAUSE

Add text:

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
If you want to use INSERT ... RETURNING or UPDATE ... RETURNING with your D=
O=20
INSTEAD RULE on a table or view, you need to take an additional step.  To t=
he=20
unconditional, final DO INSTEAD rule, you need to add a RETURNING clause,=20
which requires the following:

1. All columns of the originating table/view must be mentioned in the RULE'=
s=20
RETURNING clause;
2. The RETURNING clause will refer only to columns in the target table of t=
he=20
DO INSTEAD, not the originating table/view.
3. All data types in the RETURNING clause must match the data types of the=
=20
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=20
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 wan=
ted=20
as long as the data types match.  The calling process will only get the=20
RETURNING columns for which they ask.

An example from the "shoelaces" INSERT case above:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Continue with the Shoelaces example and the final paragraph about RETURNING=
,=20
finishing with:

"...the RETURNING clause is simply ignored for INSERT."


=2D-=20
Josh Berkus
PostgreSQL @ Sun
San Francisco