From: Josh Berkus <josh@agliodbs.com>
To: pgsql-docs@postgresql.org
Subject: Improve doc: RETURNING and RULES
Date: Wed, 12 Sep 2007 11:44:57 -0700
Message-ID: <200709121144.57176.josh@agliodbs.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: Improve doc: RETURNING and RULES
In-Reply-To: <200709121144.57176.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox