Re: INSERT ... RETURNING

This has been saved for the 8.2 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Omar Kilani wrote:
> Bruce,
>
> > Are you still working on completing this?
>
> Attached is my latest rewrite of this patch.
>
> Basically, it adds RETURNING to INSERT/UPDATE/DELETE, and allows
> arbitrary expressions.
>
> I'm still not sure how to handle DELETE ... USING and UPDATE ... FROM as
> I don't see a way to project the expressions against multiple relations
> with just an EState to work from.
>
> That said, Tom has suggested I redo this using resjunk -- so I'll do
> just that for my next revision.
>
> Regards,
> Omar

> Index: doc/src/sgml/keywords.sgml
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/doc/src/sgml/keywords.sgml,v
> retrieving revision 2.15
> diff -C6 -r2.15 keywords.sgml
> *** doc/src/sgml/keywords.sgml 27 Nov 2004 21:27:06 -0000 2.15
> --- doc/src/sgml/keywords.sgml 30 Jul 2005 06:37:02 -0000
> ***************
> *** 3234,3245 ****
> --- 3234,3252 ----
> <entry></entry>
> <entry>non-reserved</entry>
> <entry>non-reserved</entry>
> <entry>non-reserved</entry>
> </row>
> <row>
> + <entry><token>RETURNING</token></entry>
> + <entry>reserved</entry>
> + <entry></entry>
> + <entry></entry>
> + <entry></entry>
> + </row>
> + <row>
> <entry><token>RETURNS</token></entry>
> <entry>non-reserved</entry>
> <entry>reserved</entry>
> <entry>reserved</entry>
> <entry></entry>
> </row>
> Index: doc/src/sgml/ref/delete.sgml
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/doc/src/sgml/ref/delete.sgml,v
> retrieving revision 1.24
> diff -C6 -r1.24 delete.sgml
> *** doc/src/sgml/ref/delete.sgml 8 Apr 2005 00:59:58 -0000 1.24
> --- doc/src/sgml/ref/delete.sgml 30 Jul 2005 06:37:02 -0000
> ***************
> *** 20,31 ****
> --- 20,32 ----
>
> <refsynopsisdiv>
> <synopsis>
> DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable>
> [ USING <replaceable class="PARAMETER">usinglist</replaceable> ]
> [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
> + [ RETURNING <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
> </synopsis>
> </refsynopsisdiv>
>
> <refsect1>
> <title>Description</title>
>
> ***************
> *** 102,131 ****
> A value expression that returns a value of type
> <type>boolean</type> that determines the rows which are to be
> deleted.
> </para>
> </listitem>
> </varlistentry>
> </variablelist>
> </refsect1>
>
> <refsect1>
> <title>Outputs</title>
>
> <para>
> ! On successful completion, a <command>DELETE</> command returns a command
> ! tag of the form
> <screen>
> DELETE <replaceable class="parameter">count</replaceable>
> </screen>
> The <replaceable class="parameter">count</replaceable> is the number
> of rows deleted. If <replaceable class="parameter">count</replaceable> is
> 0, no rows matched the <replaceable
> class="parameter">condition</replaceable> (this is not considered
> an error).
> </para>
> </refsect1>
>
> <refsect1>
> <title>Notes</title>
>
> <para>
> --- 103,155 ----
> A value expression that returns a value of type
> <type>boolean</type> that determines the rows which are to be
> deleted.
> </para>
> </listitem>
> </varlistentry>
> +
> + <varlistentry>
> + <term>
> + <literal>RETURNING</literal>
> + <replaceable class="PARAMETER">expression</replaceable> [, ...]
> + </term>
> + <listitem>
> + <para>
> + An optional list of expressions to return.
> + </para>
> + </listitem>
> + </varlistentry>
> </variablelist>
> </refsect1>
>
> <refsect1>
> <title>Outputs</title>
>
> <para>
> ! On successful completion, a <command>DELETE</> command without a
> ! <literal>RETURNING</literal> clause returns a command tag of the form
> <screen>
> DELETE <replaceable class="parameter">count</replaceable>
> </screen>
> The <replaceable class="parameter">count</replaceable> is the number
> of rows deleted. If <replaceable class="parameter">count</replaceable> is
> 0, no rows matched the <replaceable
> class="parameter">condition</replaceable> (this is not considered
> an error).
> </para>
> +
> + <indexterm zone="sql-delete">
> + <primary>RETURNING</primary>
> + <secondary>DELETE</secondary>
> + </indexterm>
> +
> + <para>
> + If a <literal>RETURNING</literal> clause is present, the expression
> + specified is evaluated for each deleted row and the result is
> + returned.
> + </para>
> </refsect1>
>
> <refsect1>
> <title>Notes</title>
>
> <para>
> ***************
> *** 158,183 ****
> <programlisting>
> DELETE FROM films WHERE kind &lt;&gt; 'Musical';
> </programlisting>
> </para>
>
> <para>
> Clear the table <literal>films</literal>:
> <programlisting>
> DELETE FROM films;
> </programlisting>
> </para>
> </refsect1>
>
> <refsect1>
> <title>Compatibility</title>
>
> <para>
> This command conforms to the SQL standard, except that the
> ! <literal>USING</> clause and the ability to reference other tables
> ! in the <literal>WHERE</> clause are <productname>PostgreSQL</>
> extensions.
> </para>
> </refsect1>
> </refentry>
>
> <!-- Keep this comment at the end of the file
> --- 182,220 ----
> <programlisting>
> DELETE FROM films WHERE kind &lt;&gt; 'Musical';
> </programlisting>
> </para>
>
> <para>
> + Delete all films but comedies returning film titles using the
> + <literal>RETURNING</literal> clause:
> + <programlisting>
> + DELETE FROM films WHERE kind &lt;&gt; 'Comedy' RETURNING title;
> +
> + title
> + ---------
> + Yojimbo
> + </programlisting>
> + </para>
> +
> + <para>
> Clear the table <literal>films</literal>:
> <programlisting>
> DELETE FROM films;
> </programlisting>
> </para>
> </refsect1>
>
> <refsect1>
> <title>Compatibility</title>
>
> <para>
> This command conforms to the SQL standard, except that the
> ! <literal>RETURNING</> clause, the <literal>USING</> clause
> ! and the ability to reference other tables in the
> ! <literal>WHERE</> clause are <productname>PostgreSQL</>
> extensions.
> </para>
> </refsect1>
> </refentry>
>
> <!-- Keep this comment at the end of the file
> Index: doc/src/sgml/ref/insert.sgml
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/doc/src/sgml/ref/insert.sgml,v
> retrieving revision 1.29
> diff -C6 -r1.29 insert.sgml
> *** doc/src/sgml/ref/insert.sgml 9 Jan 2005 05:57:45 -0000 1.29
> --- doc/src/sgml/ref/insert.sgml 30 Jul 2005 06:37:02 -0000
> ***************
> *** 19,30 ****
> --- 19,31 ----
> </indexterm>
>
> <refsynopsisdiv>
> <synopsis>
> INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
> { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) | <replaceable class="PARAMETER">query</replaceable> }
> + [ RETURNING <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
> </synopsis>
> </refsynopsisdiv>
>
> <refsect1>
> <title>Description</title>
>
> ***************
> *** 125,155 ****
> A query (<command>SELECT</command> statement) that supplies the
> rows to be inserted. Refer to the <command>SELECT</command>
> statement for a description of the syntax.
> </para>
> </listitem>
> </varlistentry>
> </variablelist>
> </refsect1>
>
> <refsect1>
> <title>Outputs</title>
>
> <para>
> ! On successful completion, an <command>INSERT</> command returns a command
> ! tag of the form
> <screen>
> INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>
> </screen>
> The <replaceable class="parameter">count</replaceable> is the number
> of rows inserted. If <replaceable class="parameter">count</replaceable>
> is exactly one, and the target table has OIDs, then
> <replaceable class="parameter">oid</replaceable> is the
> <acronym>OID</acronym> assigned to the inserted row. Otherwise
> <replaceable class="parameter">oid</replaceable> is zero.
> </para>
> </refsect1>
>
> <refsect1>
> <title>Examples</title>
>
> <para>
> --- 126,179 ----
> A query (<command>SELECT</command> statement) that supplies the
> rows to be inserted. Refer to the <command>SELECT</command>
> statement for a description of the syntax.
> </para>
> </listitem>
> </varlistentry>
> +
> + <varlistentry>
> + <term>
> + <literal>RETURNING</literal>
> + <replaceable class="PARAMETER">expression</replaceable> [, ...]
> + </term>
> + <listitem>
> + <para>
> + An optional list of expressions to return.
> + </para>
> + </listitem>
> + </varlistentry>
> </variablelist>
> </refsect1>
>
> <refsect1>
> <title>Outputs</title>
>
> <para>
> ! On successful completion, an <command>INSERT</> command without a
> ! <literal>RETURNING</literal> clause returns a command tag of the form
> <screen>
> INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>
> </screen>
> The <replaceable class="parameter">count</replaceable> is the number
> of rows inserted. If <replaceable class="parameter">count</replaceable>
> is exactly one, and the target table has OIDs, then
> <replaceable class="parameter">oid</replaceable> is the
> <acronym>OID</acronym> assigned to the inserted row. Otherwise
> <replaceable class="parameter">oid</replaceable> is zero.
> </para>
> +
> + <indexterm zone="sql-insert">
> + <primary>RETURNING</primary>
> + <secondary>INSERT</secondary>
> + </indexterm>
> +
> + <para>
> + If a <literal>RETURNING</literal> clause is present, the expression
> + specified is evaluated as per the values derived for each inserted
> + row and the result is returned.
> + </para>
> </refsect1>
>
> <refsect1>
> <title>Examples</title>
>
> <para>
> ***************
> *** 199,210 ****
> --- 223,247 ----
> <programlisting>
> INSERT INTO films SELECT * FROM tmp_films WHERE date_prod &lt; '2004-05-07';
> </programlisting>
> </para>
>
> <para>
> + This example inserts a row into the table <literal>films</literal>,
> + and returns the value of the <literal>film_id</literal> column using
> + the <literal>RETURNING</literal> clause.
> + <programlisting>
> + INSERT INTO films (title) VALUES ('Yojimbo') RETURNING film_id;
> +
> + film_id
> + ---------
> + 123
> + </programlisting>
> + </para>
> +
> + <para>
> This example inserts into array columns:
>
> <programlisting>
> -- Create an empty 3x3 gameboard for noughts-and-crosses
> -- (these commands create the same board)
> INSERT INTO tictactoe (game, board[1:3][1:3])
> ***************
> *** 216,228 ****
> </refsect1>
>
> <refsect1>
> <title>Compatibility</title>
>
> <para>
> ! <command>INSERT</command> conforms to the SQL standard. The case in
> which a column name list is omitted, but not all the columns are
> filled from the <literal>VALUES</> clause or <replaceable>query</>,
> is disallowed by the standard.
> </para>
>
> <para>
> --- 253,267 ----
> </refsect1>
>
> <refsect1>
> <title>Compatibility</title>
>
> <para>
> ! <command>INSERT</command> conforms to the SQL standard except
> ! that the <literal>RETURNING</literal> clause is a
> ! <productname>PostgreSQL</productname> extension. The case in
> which a column name list is omitted, but not all the columns are
> filled from the <literal>VALUES</> clause or <replaceable>query</>,
> is disallowed by the standard.
> </para>
>
> <para>
> Index: doc/src/sgml/ref/update.sgml
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/doc/src/sgml/ref/update.sgml,v
> retrieving revision 1.32
> diff -C6 -r1.32 update.sgml
> *** doc/src/sgml/ref/update.sgml 9 Jan 2005 05:57:45 -0000 1.32
> --- doc/src/sgml/ref/update.sgml 30 Jul 2005 06:37:02 -0000
> ***************
> *** 20,31 ****
> --- 20,32 ----
>
> <refsynopsisdiv>
> <synopsis>
> UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> SET <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...]
> [ FROM <replaceable class="PARAMETER">fromlist</replaceable> ]
> [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
> + [ RETURNING <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
> </synopsis>
> </refsynopsisdiv>
>
> <refsect1>
> <title>Description</title>
>
> ***************
> *** 128,157 ****
> An expression that returns a value of type <type>boolean</type>.
> Only rows for which this expression returns <literal>true</>
> will be updated.
> </para>
> </listitem>
> </varlistentry>
> </variablelist>
> </refsect1>
>
> <refsect1>
> <title>Outputs</title>
>
> <para>
> ! On successful completion, an <command>UPDATE</> command returns a command
> ! tag of the form
> <screen>
> UPDATE <replaceable class="parameter">count</replaceable>
> </screen>
> The <replaceable class="parameter">count</replaceable> is the number
> of rows updated. If <replaceable class="parameter">count</replaceable> is
> 0, no rows matched the <replaceable
> class="parameter">condition</replaceable> (this is not considered
> an error).
> </para>
> </refsect1>
>
> <refsect1>
> <title>Notes</title>
>
> <para>
> --- 129,181 ----
> An expression that returns a value of type <type>boolean</type>.
> Only rows for which this expression returns <literal>true</>
> will be updated.
> </para>
> </listitem>
> </varlistentry>
> +
> + <varlistentry>
> + <term>
> + <literal>RETURNING</literal>
> + <replaceable class="PARAMETER">expression</replaceable> [, ...]
> + </term>
> + <listitem>
> + <para>
> + An optional list of expressions to return.
> + </para>
> + </listitem>
> + </varlistentry>
> </variablelist>
> </refsect1>
>
> <refsect1>
> <title>Outputs</title>
>
> <para>
> ! On successful completion, an <command>UPDATE</> command without a
> ! <literal>RETURNING</literal> clause returns a command tag of the form
> <screen>
> UPDATE <replaceable class="parameter">count</replaceable>
> </screen>
> The <replaceable class="parameter">count</replaceable> is the number
> of rows updated. If <replaceable class="parameter">count</replaceable> is
> 0, no rows matched the <replaceable
> class="parameter">condition</replaceable> (this is not considered
> an error).
> </para>
> +
> + <indexterm zone="sql-update">
> + <primary>RETURNING</primary>
> + <secondary>UPDATE</secondary>
> + </indexterm>
> +
> + <para>
> + If a <literal>RETURNING</literal> clause is present, the expression
> + specified is evaluated as per the new values for each updated row
> + and the result is returned.
> + </para>
> </refsect1>
>
> <refsect1>
> <title>Notes</title>
>
> <para>
> ***************
> *** 210,221 ****
> --- 234,255 ----
> <literal>WHERE</literal> clause:
> <programlisting>
> UPDATE employees SET sales_count = sales_count + 1 WHERE id =
> (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');
> </programlisting>
>
> + Return the sales count of the salesperson after incrementing it using
> + the <literal>RETURNING</literal> clause:
> + <programlisting>
> + UPDATE employees SET sales_count = sales_count + 1 RETURNING sales_count
> +
> + sales_count
> + -------------
> + 11
> + </programlisting>
> +
> Attempt to insert a new stock item along with the quantity of stock. If
> the item already exists, instead update the stock count of the existing
> item. To do this without failing the entire transaction, use savepoints.
> <programlisting>
> BEGIN;
> -- other operations
> ***************
> *** 233,246 ****
>
> <refsect1>
> <title>Compatibility</title>
>
> <para>
> This command conforms to the <acronym>SQL</acronym> standard, except
> ! that the <literal>FROM</literal> clause is a
> ! <productname>PostgreSQL</productname> extension.
> </para>
>
> <para>
> Some other database systems offer a <literal>FROM</> option in which
> the target table is supposed to be listed again within <literal>FROM</>.
> That is not how <productname>PostgreSQL</productname> interprets
> --- 267,280 ----
>
> <refsect1>
> <title>Compatibility</title>
>
> <para>
> This command conforms to the <acronym>SQL</acronym> standard, except
> ! that the <literal>FROM</literal> clause and the <literal>RETURNING</literal>
> ! clause are <productname>PostgreSQL</productname> extensions.
> </para>
>
> <para>
> Some other database systems offer a <literal>FROM</> option in which
> the target table is supposed to be listed again within <literal>FROM</>.
> That is not how <productname>PostgreSQL</productname> interprets
> Index: src/backend/access/common/printtup.c
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/backend/access/common/printtup.c,v
> retrieving revision 1.91
> diff -C6 -r1.91 printtup.c
> *** src/backend/access/common/printtup.c 22 Jun 2005 17:45:45 -0000 1.91
> --- src/backend/access/common/printtup.c 30 Jul 2005 06:37:02 -0000
> ***************
> *** 16,27 ****
> --- 16,28 ----
> #include "postgres.h"
>
> #include "access/heapam.h"
> #include "access/printtup.h"
> #include "libpq/libpq.h"
> #include "libpq/pqformat.h"
> + #include "executor/executor.h"
> #include "tcop/pquery.h"
> #include "utils/lsyscache.h"
> #include "utils/portal.h"
>
>
> static void printtup_startup(DestReceiver *self, int operation,
> ***************
> *** 106,117 ****
> --- 107,120 ----
>
> static void
> printtup_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
> {
> DR_printtup *myState = (DR_printtup *) self;
> Portal portal = myState->portal;
> + List *returning = ((Query *) linitial(portal->parseTrees))->returning;
> + bool withReturning = (returning != NIL);
>
> if (PG_PROTOCOL_MAJOR(FrontendProtocol) < 3)
> {
> /*
> * Send portal name to frontend (obsolete cruft, gone in proto
> * 3.0)
> ***************
> *** 131,143 ****
> * descriptions, then we send back the tuple descriptor of the tuples.
> */
> if (operation == CMD_SELECT && myState->sendDescrip)
> SendRowDescriptionMessage(typeinfo,
> FetchPortalTargetList(portal),
> portal->formats);
> !
> /* ----------------
> * We could set up the derived attr info at this time, but we postpone it
> * until the first call of printtup, for 2 reasons:
> * 1. We don't waste time (compared to the old way) if there are no
> * tuples at all to output.
> * 2. Checking in printtup allows us to handle the case that the tuples
> --- 134,150 ----
> * descriptions, then we send back the tuple descriptor of the tuples.
> */
> if (operation == CMD_SELECT && myState->sendDescrip)
> SendRowDescriptionMessage(typeinfo,
> FetchPortalTargetList(portal),
> portal->formats);
> ! else if (withReturning)
> ! SendRowDescriptionMessage(ExecTypeFromTL(returning, false),
> ! returning,
> ! portal->formats);
> !
> /* ----------------
> * We could set up the derived attr info at this time, but we postpone it
> * until the first call of printtup, for 2 reasons:
> * 1. We don't waste time (compared to the old way) if there are no
> * tuples at all to output.
> * 2. Checking in printtup allows us to handle the case that the tuples
> ***************
> *** 300,312 ****
>
> pq_sendint(&buf, natts, 2);
>
> /*
> * send the attributes of this tuple
> */
> ! for (i = 0; i < natts; ++i)
> {
> PrinttupAttrInfo *thisState = myState->myinfo + i;
> Datum origattr = slot->tts_values[i],
> attr;
>
> if (slot->tts_isnull[i])
> --- 307,319 ----
>
> pq_sendint(&buf, natts, 2);
>
> /*
> * send the attributes of this tuple
> */
> ! for (i = 0; i < natts; i++)
> {
> PrinttupAttrInfo *thisState = myState->myinfo + i;
> Datum origattr = slot->tts_values[i],
> attr;
>
> if (slot->tts_isnull[i])
> Index: src/backend/executor/execMain.c
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/backend/executor/execMain.c,v
> retrieving revision 1.251
> diff -C6 -r1.251 execMain.c
> *** src/backend/executor/execMain.c 28 Jun 2005 05:08:55 -0000 1.251
> --- src/backend/executor/execMain.c 30 Jul 2005 06:59:06 -0000
> ***************
> *** 80,97 ****
> long numberTuples,
> ScanDirection direction,
> DestReceiver *dest);
> static void ExecSelect(TupleTableSlot *slot,
> DestReceiver *dest,
> EState *estate);
> ! static void ExecInsert(TupleTableSlot *slot, ItemPointer tupleid,
> ! EState *estate);
> ! static void ExecDelete(TupleTableSlot *slot, ItemPointer tupleid,
> ! EState *estate);
> ! static void ExecUpdate(TupleTableSlot *slot, ItemPointer tupleid,
> ! EState *estate);
> static TupleTableSlot *EvalPlanQualNext(EState *estate);
> static void EndEvalPlanQual(EState *estate);
> static void ExecCheckRTEPerms(RangeTblEntry *rte);
> static void ExecCheckXactReadOnly(Query *parsetree);
> static void EvalPlanQualStart(evalPlanQual *epq, EState *estate,
> evalPlanQual *priorepq);
> --- 80,97 ----
> long numberTuples,
> ScanDirection direction,
> DestReceiver *dest);
> static void ExecSelect(TupleTableSlot *slot,
> DestReceiver *dest,
> EState *estate);
> ! static void ExecInsert(TupleTableSlot *slot, DestReceiver *dest,
> ! ItemPointer tupleid, EState *estate);
> ! static void ExecDelete(TupleTableSlot *slot, DestReceiver *dest,
> ! ItemPointer tupleid, EState *estate);
> ! static void ExecUpdate(TupleTableSlot *slot, DestReceiver *dest,
> ! ItemPointer tupleid, EState *estate);
> static TupleTableSlot *EvalPlanQualNext(EState *estate);
> static void EndEvalPlanQual(EState *estate);
> static void ExecCheckRTEPerms(RangeTblEntry *rte);
> static void ExecCheckXactReadOnly(Query *parsetree);
> static void EvalPlanQualStart(evalPlanQual *epq, EState *estate,
> evalPlanQual *priorepq);
> ***************
> *** 155,166 ****
> --- 155,169 ----
> /*
> * Copy other important information into the EState
> */
> estate->es_snapshot = queryDesc->snapshot;
> estate->es_crosscheck_snapshot = queryDesc->crosscheck_snapshot;
> estate->es_instrument = queryDesc->doInstrument;
> + estate->es_returning =
> + ExecTransformReturning(queryDesc->parsetree->returning,
> + estate);
>
> /*
> * Initialize the plan state tree
> */
> InitPlan(queryDesc, explainOnly);
>
> ***************
> *** 1252,1274 ****
> dest, /* destination's tuple-receiver obj */
> estate);
> result = slot;
> break;
>
> case CMD_INSERT:
> ! ExecInsert(slot, tupleid, estate);
> result = NULL;
> break;
>
> case CMD_DELETE:
> ! ExecDelete(slot, tupleid, estate);
> result = NULL;
> break;
>
> case CMD_UPDATE:
> ! ExecUpdate(slot, tupleid, estate);
> result = NULL;
> break;
>
> default:
> elog(ERROR, "unrecognized operation code: %d",
> (int) operation);
> --- 1255,1277 ----
> dest, /* destination's tuple-receiver obj */
> estate);
> result = slot;
> break;
>
> case CMD_INSERT:
> ! ExecInsert(slot, dest, tupleid, estate);
> result = NULL;
> break;
>
> case CMD_DELETE:
> ! ExecDelete(slot, dest, tupleid, estate);
> result = NULL;
> break;
>
> case CMD_UPDATE:
> ! ExecUpdate(slot, dest, tupleid, estate);
> result = NULL;
> break;
>
> default:
> elog(ERROR, "unrecognized operation code: %d",
> (int) operation);
> ***************
> *** 1361,1372 ****
> --- 1364,1376 ----
> * the base relation and insert appropriate tuples into the
> * index relations.
> * ----------------------------------------------------------------
> */
> static void
> ExecInsert(TupleTableSlot *slot,
> + DestReceiver *dest,
> ItemPointer tupleid,
> EState *estate)
> {
> HeapTuple tuple;
> ResultRelInfo *resultRelInfo;
> Relation resultRelationDesc;
> ***************
> *** 1419,1430 ****
> --- 1423,1444 ----
> * insert the tuple
> */
> newId = heap_insert(resultRelationDesc, tuple,
> estate->es_snapshot->curcid,
> true, true);
>
> + if (estate->es_returning != NULL)
> + {
> + TupleTableSlot *retSlot = ExecReturning(slot, estate);
> + /*
> + * send the tuple to the destination
> + */
> + (*dest->receiveSlot) (retSlot, dest);
> + ExecClearTuple(retSlot);
> + }
> +
> IncrAppended();
> (estate->es_processed)++;
> estate->es_lastoid = newId;
> setLastTid(&(tuple->t_self));
>
> /*
> ***************
> *** 1448,1459 ****
> --- 1462,1474 ----
> * DELETE is like UPDATE, we delete the tuple and its
> * index tuples.
> * ----------------------------------------------------------------
> */
> static void
> ExecDelete(TupleTableSlot *slot,
> + DestReceiver *dest,
> ItemPointer tupleid,
> EState *estate)
> {
> ResultRelInfo *resultRelInfo;
> Relation resultRelationDesc;
> ItemPointerData ctid;
> ***************
> *** 1522,1533 ****
> --- 1537,1563 ----
>
> default:
> elog(ERROR, "unrecognized heap_delete status: %u", result);
> return;
> }
>
> + if (estate->es_returning != NULL)
> + {
> + TupleTableSlot *deletedSlot;
> + TupleTableSlot *retSlot;
> +
> + deletedSlot = ExecGetDeletedSlot(tupleid, estate);
> + retSlot = ExecReturning(deletedSlot, estate);
> + /*
> + * send the tuple to the destination
> + */
> + (*dest->receiveSlot) (retSlot, dest);
> + ExecClearTuple(retSlot);
> + ExecClearTuple(deletedSlot);
> + }
> +
> IncrDeleted();
> (estate->es_processed)++;
>
> /*
> * Note: Normally one would think that we have to delete index tuples
> * associated with the heap tuple now..
> ***************
> *** 1551,1562 ****
> --- 1581,1593 ----
> * is, we don't want to get stuck in an infinite loop
> * which corrupts your database..
> * ----------------------------------------------------------------
> */
> static void
> ExecUpdate(TupleTableSlot *slot,
> + DestReceiver *dest,
> ItemPointer tupleid,
> EState *estate)
> {
> HeapTuple tuple;
> ResultRelInfo *resultRelInfo;
> Relation resultRelationDesc;
> ***************
> *** 1666,1677 ****
> --- 1697,1718 ----
>
> default:
> elog(ERROR, "unrecognized heap_update status: %u", result);
> return;
> }
>
> + if (estate->es_returning != NULL)
> + {
> + TupleTableSlot *retSlot = ExecReturning(slot, estate);
> + /*
> + * send the tuple to the destination
> + */
> + (*dest->receiveSlot) (retSlot, dest);
> + ExecClearTuple(retSlot);
> + }
> +
> IncrReplaced();
> (estate->es_processed)++;
>
> /*
> * Note: instead of having to update the old index tuples associated
> * with the heap tuple, all we do is form and insert new index tuples.
> Index: src/backend/executor/execUtils.c
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/backend/executor/execUtils.c,v
> retrieving revision 1.124
> diff -C6 -r1.124 execUtils.c
> *** src/backend/executor/execUtils.c 20 Jun 2005 18:37:01 -0000 1.124
> --- src/backend/executor/execUtils.c 30 Jul 2005 06:41:21 -0000
> ***************
> *** 1062,1067 ****
> --- 1062,1152 ----
> (*ecxt_callback->function) (ecxt_callback->arg);
> pfree(ecxt_callback);
> }
>
> MemoryContextSwitchTo(oldcontext);
> }
> +
> + TupleTableSlot *
> + ExecReturning(TupleTableSlot *slot,
> + EState *estate)
> + {
> + TupleTableSlot *retSlot,
> + *scanTupleSave;
> + ExprContext *returningExprContext;
> + ProjectionInfo *retProject;
> +
> + returningExprContext = (ExprContext *) linitial(estate->es_exprcontexts);
> +
> + scanTupleSave = returningExprContext->ecxt_scantuple;
> + returningExprContext->ecxt_scantuple = slot;
> +
> + retProject = ExecBuildProjectionInfo(estate->es_returning->retExprs,
> + returningExprContext,
> + estate->es_returning->retSlot);
> +
> + retSlot = ExecProject(retProject, NULL);
> + returningExprContext->ecxt_scantuple = scanTupleSave;
> + return retSlot;
> + }
> +
> + ReturningState *
> + ExecTransformReturning(List *returning,
> + EState *estate)
> + {
> + ReturningState *retState;
> + List *retExprs = NIL;
> + ListCell *retElem;
> + int i = 1;
> +
> + if (returning == NIL)
> + return NULL;
> +
> + retState = palloc(1 * sizeof(ReturningState));
> +
> + foreach (retElem, returning)
> + {
> + TargetEntry *tle;
> + GenericExprState *gstate;
> +
> + tle = (TargetEntry *) lfirst(retElem);
> + tle->resno = i++;
> + gstate = makeNode(GenericExprState);
> + gstate->xprstate.expr = (Expr *) tle;
> + gstate->xprstate.evalfunc = NULL;
> + gstate->arg = ExecPrepareExpr(tle->expr, estate);
> +
> + retExprs = lappend(retExprs, gstate);
> + }
> +
> + retState->retTupleDesc = ExecTypeFromTL(returning, false);
> + retState->retExprs = retExprs;
> + retState->retSlot = MakeSingleTupleTableSlot(retState->retTupleDesc);
> +
> + return retState;
> + }
> +
> + TupleTableSlot *
> + ExecGetDeletedSlot(ItemPointer tupleid,
> + EState *estate)
> + {
> + TupleTableSlot *retSlot = NULL;
> + HeapTupleData retTuple;
> + Buffer buffer;
> +
> + retTuple.t_self = *tupleid;
> +
> + if (heap_fetch(estate->es_result_relation_info->ri_RelationDesc,
> + SnapshotNow,
> + &retTuple,
> + &buffer,
> + false,
> + NULL))
> + {
> + retSlot = MakeSingleTupleTableSlot(estate->es_result_relation_info->ri_RelationDesc->rd_att);
> + ExecStoreTuple(&retTuple, retSlot, InvalidBuffer, false);
> + slot_getallattrs(retSlot);
> + ReleaseBuffer(buffer);
> + }
> +
> + return retSlot;
> + }
> Index: src/backend/nodes/copyfuncs.c
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/backend/nodes/copyfuncs.c,v
> retrieving revision 1.312
> diff -C6 -r1.312 copyfuncs.c
> *** src/backend/nodes/copyfuncs.c 26 Jul 2005 16:38:27 -0000 1.312
> --- src/backend/nodes/copyfuncs.c 30 Jul 2005 06:37:02 -0000
> ***************
> *** 1605,1616 ****
> --- 1605,1617 ----
> COPY_SCALAR_FIELD(hasSubLinks);
> COPY_NODE_FIELD(rtable);
> COPY_NODE_FIELD(jointree);
> COPY_NODE_FIELD(rowMarks);
> COPY_SCALAR_FIELD(forUpdate);
> COPY_NODE_FIELD(targetList);
> + COPY_NODE_FIELD(returning);
> COPY_NODE_FIELD(groupClause);
> COPY_NODE_FIELD(havingQual);
> COPY_NODE_FIELD(distinctClause);
> COPY_NODE_FIELD(sortClause);
> COPY_NODE_FIELD(limitOffset);
> COPY_NODE_FIELD(limitCount);
> ***************
> *** 1626,1649 ****
> --- 1627,1652 ----
> InsertStmt *newnode = makeNode(InsertStmt);
>
> COPY_NODE_FIELD(relation);
> COPY_NODE_FIELD(cols);
> COPY_NODE_FIELD(targetList);
> COPY_NODE_FIELD(selectStmt);
> + COPY_NODE_FIELD(returning);
>
> return newnode;
> }
>
> static DeleteStmt *
> _copyDeleteStmt(DeleteStmt *from)
> {
> DeleteStmt *newnode = makeNode(DeleteStmt);
>
> COPY_NODE_FIELD(relation);
> COPY_NODE_FIELD(whereClause);
> COPY_NODE_FIELD(usingClause);
> + COPY_NODE_FIELD(returning);
>
> return newnode;
> }
>
> static UpdateStmt *
> _copyUpdateStmt(UpdateStmt *from)
> ***************
> *** 1651,1662 ****
> --- 1654,1666 ----
> UpdateStmt *newnode = makeNode(UpdateStmt);
>
> COPY_NODE_FIELD(relation);
> COPY_NODE_FIELD(targetList);
> COPY_NODE_FIELD(whereClause);
> COPY_NODE_FIELD(fromClause);
> + COPY_NODE_FIELD(returning);
>
> return newnode;
> }
>
> static SelectStmt *
> _copySelectStmt(SelectStmt *from)
> Index: src/backend/nodes/equalfuncs.c
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/backend/nodes/equalfuncs.c,v
> retrieving revision 1.249
> diff -C6 -r1.249 equalfuncs.c
> *** src/backend/nodes/equalfuncs.c 26 Jul 2005 16:38:27 -0000 1.249
> --- src/backend/nodes/equalfuncs.c 30 Jul 2005 06:37:02 -0000
> ***************
> *** 642,653 ****
> --- 642,654 ----
> COMPARE_SCALAR_FIELD(hasSubLinks);
> COMPARE_NODE_FIELD(rtable);
> COMPARE_NODE_FIELD(jointree);
> COMPARE_NODE_FIELD(rowMarks);
> COMPARE_SCALAR_FIELD(forUpdate);
> COMPARE_NODE_FIELD(targetList);
> + COMPARE_NODE_FIELD(returning);
> COMPARE_NODE_FIELD(groupClause);
> COMPARE_NODE_FIELD(havingQual);
> COMPARE_NODE_FIELD(distinctClause);
> COMPARE_NODE_FIELD(sortClause);
> COMPARE_NODE_FIELD(limitOffset);
> COMPARE_NODE_FIELD(limitCount);
> ***************
> *** 661,693 ****
> --- 662,697 ----
> _equalInsertStmt(InsertStmt *a, InsertStmt *b)
> {
> COMPARE_NODE_FIELD(relation);
> COMPARE_NODE_FIELD(cols);
> COMPARE_NODE_FIELD(targetList);
> COMPARE_NODE_FIELD(selectStmt);
> + COMPARE_NODE_FIELD(returning);
>
> return true;
> }
>
> static bool
> _equalDeleteStmt(DeleteStmt *a, DeleteStmt *b)
> {
> COMPARE_NODE_FIELD(relation);
> COMPARE_NODE_FIELD(whereClause);
> COMPARE_NODE_FIELD(usingClause);
> + COMPARE_NODE_FIELD(returning);
>
> return true;
> }
>
> static bool
> _equalUpdateStmt(UpdateStmt *a, UpdateStmt *b)
> {
> COMPARE_NODE_FIELD(relation);
> COMPARE_NODE_FIELD(targetList);
> COMPARE_NODE_FIELD(whereClause);
> COMPARE_NODE_FIELD(fromClause);
> + COMPARE_NODE_FIELD(returning);
>
> return true;
> }
>
> static bool
> _equalSelectStmt(SelectStmt *a, SelectStmt *b)
> Index: src/backend/nodes/outfuncs.c
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/backend/nodes/outfuncs.c,v
> retrieving revision 1.258
> diff -C6 -r1.258 outfuncs.c
> *** src/backend/nodes/outfuncs.c 2 Jul 2005 23:00:39 -0000 1.258
> --- src/backend/nodes/outfuncs.c 30 Jul 2005 06:37:02 -0000
> ***************
> *** 1460,1471 ****
> --- 1460,1472 ----
> WRITE_BOOL_FIELD(hasSubLinks);
> WRITE_NODE_FIELD(rtable);
> WRITE_NODE_FIELD(jointree);
> WRITE_NODE_FIELD(rowMarks);
> WRITE_BOOL_FIELD(forUpdate);
> WRITE_NODE_FIELD(targetList);
> + WRITE_NODE_FIELD(returning);
> WRITE_NODE_FIELD(groupClause);
> WRITE_NODE_FIELD(havingQual);
> WRITE_NODE_FIELD(distinctClause);
> WRITE_NODE_FIELD(sortClause);
> WRITE_NODE_FIELD(limitOffset);
> WRITE_NODE_FIELD(limitCount);
> Index: src/backend/nodes/readfuncs.c
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/backend/nodes/readfuncs.c,v
> retrieving revision 1.180
> diff -C6 -r1.180 readfuncs.c
> *** src/backend/nodes/readfuncs.c 28 Jun 2005 05:08:57 -0000 1.180
> --- src/backend/nodes/readfuncs.c 30 Jul 2005 06:37:02 -0000
> ***************
> *** 144,155 ****
> --- 144,156 ----
> READ_BOOL_FIELD(hasSubLinks);
> READ_NODE_FIELD(rtable);
> READ_NODE_FIELD(jointree);
> READ_NODE_FIELD(rowMarks);
> READ_BOOL_FIELD(forUpdate);
> READ_NODE_FIELD(targetList);
> + READ_NODE_FIELD(returning);
> READ_NODE_FIELD(groupClause);
> READ_NODE_FIELD(havingQual);
> READ_NODE_FIELD(distinctClause);
> READ_NODE_FIELD(sortClause);
> READ_NODE_FIELD(limitOffset);
> READ_NODE_FIELD(limitCount);
> Index: src/backend/parser/analyze.c
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/backend/parser/analyze.c,v
> retrieving revision 1.323
> diff -C6 -r1.323 analyze.c
> *** src/backend/parser/analyze.c 28 Jul 2005 22:27:00 -0000 1.323
> --- src/backend/parser/analyze.c 30 Jul 2005 06:37:02 -0000
> ***************
> *** 100,111 ****
> --- 100,113 ----
> List **extras_before, List **extras_after);
> static Query *transformViewStmt(ParseState *pstate, ViewStmt *stmt,
> List **extras_before, List **extras_after);
> static Query *transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt);
> static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt,
> List **extras_before, List **extras_after);
> + static List *transformReturningList(ParseState *pstate, RangeVar *relation, List *returning);
> +
> static Query *transformIndexStmt(ParseState *pstate, IndexStmt *stmt);
> static Query *transformRuleStmt(ParseState *query, RuleStmt *stmt,
> List **extras_before, List **extras_after);
> static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt);
> static Query *transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt);
> static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt);
> ***************
> *** 486,497 ****
> --- 488,506 ----
> */
> transformFromClause(pstate, stmt->usingClause);
>
> /* fix where clause */
> qual = transformWhereClause(pstate, stmt->whereClause, "WHERE");
>
> + /*
> + * Transform any RETURNING values to form a targetlist.
> + */
> +
> + qry->returning = transformReturningList(pstate, stmt->relation,
> + stmt->returning);
> +
> /* done building the range table and jointree */
> qry->rtable = pstate->p_rtable;
> qry->jointree = makeFromExpr(pstate->p_joinlist, qual);
>
> qry->hasSubLinks = pstate->p_hasSubLinks;
> qry->hasAggs = pstate->p_hasAggs;
> ***************
> *** 663,674 ****
> --- 672,690 ----
> * form a targetlist for the INSERT.
> */
> qry->targetList = transformTargetList(pstate, stmt->targetList);
> }
>
> /*
> + * Transform any RETURNING values to form a targetlist.
> + */
> +
> + qry->returning = transformReturningList(pstate, stmt->relation,
> + stmt->returning);
> +
> + /*
> * Now we are done with SELECT-like processing, and can get on with
> * transforming the target list to match the INSERT target columns.
> */
>
> /* Prepare to assign non-conflicting resnos to resjunk attributes */
> if (pstate->p_next_resno <= pstate->p_target_relation->rd_rel->relnatts)
> ***************
> *** 722,733 ****
> --- 738,776 ----
> if (pstate->p_hasAggs)
> parseCheckAggregates(pstate, qry);
>
> return qry;
> }
>
> + static List *
> + transformReturningList(ParseState *pstate, RangeVar *relation, List *returning)
> + {
> + List *ret = NIL;
> + RangeTblEntry *retrte;
> +
> + if (returning != NIL)
> + {
> + /*
> + * Add the RTE to the pstate if we don't have any already.
> + * This will usually happen for INSERT.
> + */
> + if (pstate->p_varnamespace == NIL)
> + {
> + retrte = addRangeTableEntry(pstate, relation,
> + makeAlias("*RETURNING*", NIL),
> + false, false);
> + addRTEtoQuery(pstate, retrte, false, true, true);
> + }
> +
> + ret = transformTargetList(pstate, returning);
> + if (ret != NIL)
> + markTargetListOrigins(pstate, ret);
> + }
> + return ret;
> + }
> +
> /*
> * transformCreateStmt -
> * transforms the "create table" statement
> * SQL92 allows constraints to be scattered all over, so thumb through
> * the columns and collect all constraints into one place.
> * If there are any implied indices (e.g. UNIQUE or PRIMARY KEY)
> ***************
> *** 2337,2348 ****
> --- 2380,2398 ----
> * do this with REPLACE in POSTQUEL so we keep the feature.
> */
> transformFromClause(pstate, stmt->fromClause);
>
> qry->targetList = transformTargetList(pstate, stmt->targetList);
>
> + /*
> + * Transform any RETURNING values to form a targetlist.
> + */
> +
> + qry->returning = transformReturningList(pstate, stmt->relation,
> + stmt->returning);
> +
> qual = transformWhereClause(pstate, stmt->whereClause, "WHERE");
>
> qry->rtable = pstate->p_rtable;
> qry->jointree = makeFromExpr(pstate->p_joinlist, qual);
>
> qry->hasSubLinks = pstate->p_hasSubLinks;
> Index: src/backend/parser/gram.y
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/backend/parser/gram.y,v
> retrieving revision 2.504
> diff -C6 -r2.504 gram.y
> *** src/backend/parser/gram.y 26 Jul 2005 22:37:50 -0000 2.504
> --- src/backend/parser/gram.y 30 Jul 2005 06:37:02 -0000
> ***************
> *** 239,250 ****
> --- 239,252 ----
> %type <withoids> OptWithOids WithOidsAs
>
> %type <list> for_locking_clause opt_for_locking_clause
> update_list
> %type <boolean> opt_all
>
> + %type <list> opt_returning_list
> +
> %type <node> join_outer join_qual
> %type <jtype> join_type
>
> %type <list> extract_list overlay_list position_list
> %type <list> substr_list trim_list
> %type <ival> opt_interval
> ***************
> *** 386,398 ****
> PRECISION PRESERVE PREPARE PREPARED PRIMARY
> PRIOR PRIVILEGES PROCEDURAL PROCEDURE
>
> QUOTE
>
> READ REAL RECHECK REFERENCES REINDEX RELATIVE_P RELEASE RENAME
> ! REPEATABLE REPLACE RESET RESTART RESTRICT RETURNS REVOKE RIGHT
> ROLE ROLLBACK ROW ROWS RULE
>
> SAVEPOINT SCHEMA SCROLL SECOND_P SECURITY SELECT SEQUENCE
> SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE
> SHOW SIMILAR SIMPLE SMALLINT SOME STABLE START STATEMENT
> STATISTICS STDIN STDOUT STORAGE STRICT_P SUBSTRING SUPERUSER_P SYMMETRIC
> --- 388,400 ----
> PRECISION PRESERVE PREPARE PREPARED PRIMARY
> PRIOR PRIVILEGES PROCEDURAL PROCEDURE
>
> QUOTE
>
> READ REAL RECHECK REFERENCES REINDEX RELATIVE_P RELEASE RENAME
> ! REPEATABLE REPLACE RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT
> ROLE ROLLBACK ROW ROWS RULE
>
> SAVEPOINT SCHEMA SCROLL SECOND_P SECURITY SELECT SEQUENCE
> SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE
> SHOW SIMILAR SIMPLE SMALLINT SOME STABLE START STATEMENT
> STATISTICS STDIN STDOUT STORAGE STRICT_P SUBSTRING SUPERUSER_P SYMMETRIC
> ***************
> *** 4879,4893 ****
> * QUERY:
> * INSERT STATEMENTS
> *
> *****************************************************************************/
>
> InsertStmt:
> ! INSERT INTO qualified_name insert_rest
> {
> $4->relation = $3;
> $$ = (Node *) $4;
> }
> ;
>
> insert_rest:
> VALUES '(' insert_target_list ')'
> --- 4881,4896 ----
> * QUERY:
> * INSERT STATEMENTS
> *
> *****************************************************************************/
>
> InsertStmt:
> ! INSERT INTO qualified_name insert_rest opt_returning_list
> {
> $4->relation = $3;
> + $4->returning = $5;
> $$ = (Node *) $4;
> }
> ;
>
> insert_rest:
> VALUES '(' insert_target_list ')'
> ***************
> *** 4941,4966 ****
> $$->name = $1;
> $$->indirection = $2;
> $$->val = NULL;
> }
> ;
>
>
> /*****************************************************************************
> *
> * QUERY:
> * DELETE STATEMENTS
> *
> *****************************************************************************/
>
> ! DeleteStmt: DELETE_P FROM relation_expr using_clause where_clause
> {
> DeleteStmt *n = makeNode(DeleteStmt);
> n->relation = $3;
> n->usingClause = $4;
> n->whereClause = $5;
> $$ = (Node *)n;
> }
> ;
>
> using_clause:
> USING from_list { $$ = $2; }
> --- 4944,4972 ----
> $$->name = $1;
> $$->indirection = $2;
> $$->val = NULL;
> }
> ;
>
> + opt_returning_list: RETURNING target_list { $$ = $2; }
> + | /*EMPTY*/ { $$ = NIL; }
>
> /*****************************************************************************
> *
> * QUERY:
> * DELETE STATEMENTS
> *
> *****************************************************************************/
>
> ! DeleteStmt: DELETE_P FROM relation_expr using_clause where_clause opt_returning_list
> {
> DeleteStmt *n = makeNode(DeleteStmt);
> n->relation = $3;
> n->usingClause = $4;
> n->whereClause = $5;
> + n->returning = $6;
> $$ = (Node *)n;
> }
> ;
>
> using_clause:
> USING from_list { $$ = $2; }
> ***************
> *** 5005,5022 ****
> --- 5011,5030 ----
> *****************************************************************************/
>
> UpdateStmt: UPDATE relation_expr
> SET update_target_list
> from_clause
> where_clause
> + opt_returning_list
> {
> UpdateStmt *n = makeNode(UpdateStmt);
> n->relation = $2;
> n->targetList = $4;
> n->fromClause = $5;
> n->whereClause = $6;
> + n->returning = $7;
> $$ = (Node *)n;
> }
> ;
>
>
> /*****************************************************************************
> ***************
> *** 7673,7685 ****
> $$->name = NULL;
> $$->indirection = NIL;
> $$->val = (Node *) makeNode(SetToDefault);
> }
> ;
>
> -
> /*****************************************************************************
> *
> * Names and constants
> *
> *****************************************************************************/
>
> --- 7681,7692 ----
> ***************
> *** 8278,8289 ****
> --- 8285,8297 ----
> | ONLY
> | OR
> | ORDER
> | PLACING
> | PRIMARY
> | REFERENCES
> + | RETURNING
> | SELECT
> | SESSION_USER
> | SOME
> | SYMMETRIC
> | TABLE
> | THEN
> Index: src/backend/parser/keywords.c
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/backend/parser/keywords.c,v
> retrieving revision 1.163
> diff -C6 -r1.163 keywords.c
> *** src/backend/parser/keywords.c 26 Jul 2005 16:38:27 -0000 1.163
> --- src/backend/parser/keywords.c 30 Jul 2005 06:37:02 -0000
> ***************
> *** 273,284 ****
> --- 273,285 ----
> {"rename", RENAME},
> {"repeatable", REPEATABLE},
> {"replace", REPLACE},
> {"reset", RESET},
> {"restart", RESTART},
> {"restrict", RESTRICT},
> + {"returning", RETURNING},
> {"returns", RETURNS},
> {"revoke", REVOKE},
> {"right", RIGHT},
> {"role", ROLE},
> {"rollback", ROLLBACK},
> {"row", ROW},
> Index: src/include/executor/executor.h
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/include/executor/executor.h,v
> retrieving revision 1.118
> diff -C6 -r1.118 executor.h
> *** src/include/executor/executor.h 16 Apr 2005 20:07:35 -0000 1.118
> --- src/include/executor/executor.h 30 Jul 2005 06:37:02 -0000
> ***************
> *** 240,246 ****
> --- 240,253 ----
> ExprContextCallbackFunction function,
> Datum arg);
> extern void UnregisterExprContextCallback(ExprContext *econtext,
> ExprContextCallbackFunction function,
> Datum arg);
>
> + extern TupleTableSlot *ExecReturning(TupleTableSlot *slot,
> + EState *estate);
> + extern ReturningState *ExecTransformReturning(List *returning,
> + EState *estate);
> + extern TupleTableSlot *ExecGetDeletedSlot(ItemPointer tupleid,
> + EState *estate);
> +
> #endif /* EXECUTOR_H */
> Index: src/include/nodes/execnodes.h
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/include/nodes/execnodes.h,v
> retrieving revision 1.136
> diff -C6 -r1.136 execnodes.h
> *** src/include/nodes/execnodes.h 26 Jun 2005 22:05:41 -0000 1.136
> --- src/include/nodes/execnodes.h 30 Jul 2005 06:47:11 -0000
> ***************
> *** 279,290 ****
> --- 279,297 ----
> FmgrInfo *ri_TrigFunctions;
> struct Instrumentation *ri_TrigInstrument;
> List **ri_ConstraintExprs;
> JunkFilter *ri_junkFilter;
> } ResultRelInfo;
>
> + typedef struct ReturningState
> + {
> + TupleDesc retTupleDesc;
> + List *retExprs;
> + TupleTableSlot *retSlot;
> + } ReturningState;
> +
> /* ----------------
> * EState information
> *
> * Master working state for an Executor invocation
> * ----------------
> */
> ***************
> *** 322,333 ****
> --- 329,341 ----
> List *es_rowMark; /* not good place, but there is no other */
> bool es_forUpdate; /* was it FOR UPDATE or FOR SHARE */
>
> bool es_instrument; /* true requests runtime instrumentation */
> bool es_select_into; /* true if doing SELECT INTO */
> bool es_into_oids; /* true to generate OIDs in SELECT INTO */
> + ReturningState *es_returning; /* list of expressions to return */
>
> List *es_exprcontexts; /* List of ExprContexts within EState */
>
> /*
> * this ExprContext is for per-output-tuple operations, such as
> * constraint checks and index-value computations. It will be reset
> Index: src/include/nodes/parsenodes.h
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/include/nodes/parsenodes.h,v
> retrieving revision 1.286
> diff -C6 -r1.286 parsenodes.h
> *** src/include/nodes/parsenodes.h 26 Jul 2005 16:38:28 -0000 1.286
> --- src/include/nodes/parsenodes.h 30 Jul 2005 06:37:02 -0000
> ***************
> *** 95,106 ****
> --- 95,108 ----
>
> bool forUpdate; /* true if rowMarks are FOR UPDATE,
> * false if they are FOR SHARE */
>
> List *targetList; /* target list (of TargetEntry) */
>
> + List *returning; /* list of columns to return */
> +
> List *groupClause; /* a list of GroupClause's */
>
> Node *havingQual; /* qualifications applied to groups */
>
> List *distinctClause; /* a list of SortClause's */
>
> ***************
> *** 597,620 ****
> --- 599,624 ----
> * An INSERT statement has *either* VALUES or SELECT, never both. If
> * VALUES, a targetList is supplied (empty for DEFAULT VALUES). If
> * SELECT, a complete SelectStmt (or set-operation tree) is supplied.
> */
> List *targetList; /* the target list (of ResTarget) */
> Node *selectStmt; /* the source SELECT */
> + List *returning; /* List of columns to return */
> } InsertStmt;
>
> /* ----------------------
> * Delete Statement
> * ----------------------
> */
> typedef struct DeleteStmt
> {
> NodeTag type;
> RangeVar *relation; /* relation to delete from */
> Node *whereClause; /* qualifications */
> List *usingClause; /* optional using clause for more tables */
> + List *returning; /* List of columns to return */
> } DeleteStmt;
>
> /* ----------------------
> * Update Statement
> * ----------------------
> */
> ***************
> *** 622,633 ****
> --- 626,638 ----
> {
> NodeTag type;
> RangeVar *relation; /* relation to update */
> List *targetList; /* the target list (of ResTarget) */
> Node *whereClause; /* qualifications */
> List *fromClause; /* optional from clause for more tables */
> + List *returning; /* List of columns to return */
> } UpdateStmt;
>
> /* ----------------------
> * Select Statement
> *
> * A "simple" SELECT is represented in the output of gram.y by a single
> Index: src/test/regress/expected/insert.out
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/test/regress/expected/insert.out,v
> retrieving revision 1.7
> diff -C6 -r1.7 insert.out
> *** src/test/regress/expected/insert.out 25 Sep 2003 06:58:06 -0000 1.7
> --- src/test/regress/expected/insert.out 30 Jul 2005 06:37:02 -0000
> ***************
> *** 5,24 ****
> insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT, DEFAULT);
> ERROR: null value in column "col2" violates not-null constraint
> insert into inserttest (col2, col3) values (3, DEFAULT);
> insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
> insert into inserttest values (DEFAULT, 5, 'test');
> insert into inserttest values (DEFAULT, 7);
> select * from inserttest;
> col1 | col2 | col3
> ------+------+---------
> | 3 | testing
> | 5 | testing
> | 5 | test
> | 7 | testing
> ! (4 rows)
>
> --
> -- insert with similar expression / target_list values (all fail)
> --
> insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT);
> ERROR: INSERT has more target columns than expressions
> --- 5,31 ----
> insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT, DEFAULT);
> ERROR: null value in column "col2" violates not-null constraint
> insert into inserttest (col2, col3) values (3, DEFAULT);
> insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
> insert into inserttest values (DEFAULT, 5, 'test');
> insert into inserttest values (DEFAULT, 7);
> + insert into inserttest (col2, col3) values (3, DEFAULT) returning col3, col1, col2, col2 * 5, least(col2, col2 * 5);
> + col3 | col1 | col2 | ?column? | least
> + ---------+------+------+----------+-------
> + testing | | 3 | 15 | 3
> + (1 row)
> +
> select * from inserttest;
> col1 | col2 | col3
> ------+------+---------
> | 3 | testing
> | 5 | testing
> | 5 | test
> | 7 | testing
> ! | 3 | testing
> ! (5 rows)
>
> --
> -- insert with similar expression / target_list values (all fail)
> --
> insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT);
> ERROR: INSERT has more target columns than expressions
> ***************
> *** 32,40 ****
> col1 | col2 | col3
> ------+------+---------
> | 3 | testing
> | 5 | testing
> | 5 | test
> | 7 | testing
> ! (4 rows)
>
> drop table inserttest;
> --- 39,48 ----
> col1 | col2 | col3
> ------+------+---------
> | 3 | testing
> | 5 | testing
> | 5 | test
> | 7 | testing
> ! | 3 | testing
> ! (5 rows)
>
> drop table inserttest;
> Index: src/test/regress/expected/join.out
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/test/regress/expected/join.out,v
> retrieving revision 1.25
> diff -C6 -r1.25 join.out
> *** src/test/regress/expected/join.out 22 Jul 2005 19:12:02 -0000 1.25
> --- src/test/regress/expected/join.out 30 Jul 2005 07:09:34 -0000
> ***************
> *** 2181,2186 ****
> --- 2181,2204 ----
> DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y;
> SELECT * FROM t3;
> x | y
> ---+---
> (0 rows)
>
> + INSERT INTO t3 VALUES (5, 20);
> + INSERT INTO t3 VALUES (6, 7);
> + INSERT INTO t3 VALUES (7, 8);
> + INSERT INTO t3 VALUES (500, 100);
> + DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y RETURNING t3.y, t3.x, t3.y + t3_other.x AS sum;
> + y | x | sum
> + -----+-----+-----
> + 7 | 6 | 13
> + 8 | 7 | 15
> + 20 | 5 | 25
> + 100 | 500 | 600
> + (4 rows)
> +
> + SELECT * FROM t3;
> + x | y
> + ---+---
> + (0 rows)
> +
> Index: src/test/regress/expected/update.out
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/test/regress/expected/update.out,v
> retrieving revision 1.1
> diff -C6 -r1.1 update.out
> *** src/test/regress/expected/update.out 26 Aug 2003 18:32:23 -0000 1.1
> --- src/test/regress/expected/update.out 30 Jul 2005 06:37:02 -0000
> ***************
> *** 19,25 ****
> --- 19,39 ----
> a | b
> ----+---
> 10 |
> 10 |
> (2 rows)
>
> + UPDATE update_test SET a = 5, b = 10 RETURNING b, a, a * 2 + b, greatest(a, b);
> + b | a | ?column? | greatest
> + ----+---+----------+----------
> + 10 | 5 | 20 | 10
> + 10 | 5 | 20 | 10
> + (2 rows)
> +
> + SELECT * FROM update_test;
> + a | b
> + ---+----
> + 5 | 10
> + 5 | 10
> + (2 rows)
> +
> DROP TABLE update_test;
> Index: src/test/regress/sql/insert.sql
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/test/regress/sql/insert.sql,v
> retrieving revision 1.2
> diff -C6 -r1.2 insert.sql
> *** src/test/regress/sql/insert.sql 24 Apr 2002 02:22:54 -0000 1.2
> --- src/test/regress/sql/insert.sql 30 Jul 2005 06:37:02 -0000
> ***************
> *** 4,15 ****
> --- 4,16 ----
> create table inserttest (col1 int4, col2 int4 NOT NULL, col3 text default 'testing');
> insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT, DEFAULT);
> insert into inserttest (col2, col3) values (3, DEFAULT);
> insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
> insert into inserttest values (DEFAULT, 5, 'test');
> insert into inserttest values (DEFAULT, 7);
> + insert into inserttest (col2, col3) values (3, DEFAULT) returning col3, col1, col2, col2 * 5, least(col2, col2 * 5);
>
> select * from inserttest;
>
> --
> -- insert with similar expression / target_list values (all fail)
> --
> Index: src/test/regress/sql/join.sql
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/test/regress/sql/join.sql,v
> retrieving revision 1.17
> diff -C6 -r1.17 join.sql
> *** src/test/regress/sql/join.sql 7 Apr 2005 15:23:06 -0000 1.17
> --- src/test/regress/sql/join.sql 30 Jul 2005 07:07:32 -0000
> ***************
> *** 370,375 ****
> --- 370,383 ----
> DELETE FROM t3 USING t1 table1 WHERE t3.x = table1.a;
> SELECT * FROM t3;
> DELETE FROM t3 USING t1 JOIN t2 USING (a) WHERE t3.x > t1.a;
> SELECT * FROM t3;
> DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y;
> SELECT * FROM t3;
> +
> + INSERT INTO t3 VALUES (5, 20);
> + INSERT INTO t3 VALUES (6, 7);
> + INSERT INTO t3 VALUES (7, 8);
> + INSERT INTO t3 VALUES (500, 100);
> +
> + DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y RETURNING t3.y, t3.x, t3.y + t3_other.x AS sum;
> + SELECT * FROM t3;
> Index: src/test/regress/sql/update.sql
> ===================================================================
> RCS file: /home/omar/cvs/cvs/pgsql/src/test/regress/sql/update.sql,v
> retrieving revision 1.2
> diff -C6 -r1.2 update.sql
> *** src/test/regress/sql/update.sql 7 Apr 2005 15:23:06 -0000 1.2
> --- src/test/regress/sql/update.sql 30 Jul 2005 06:37:02 -0000
> ***************
> *** 13,19 ****
> --- 13,23 ----
> SELECT * FROM update_test;
>
> UPDATE update_test SET a = DEFAULT, b = DEFAULT;
>
> SELECT * FROM update_test;
>
> + UPDATE update_test SET a = 5, b = 10 RETURNING b, a, a * 2 + b, greatest(a, b);
> +
> + SELECT * FROM update_test;
> +
> DROP TABLE update_test;

>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073