Thread: INSERT ... RETURNING
Hi there, Attached is a patch (by Gavin Sherry, fixed up to apply to 8.1 by me) that implements INSERT ... RETURNING functionality. It does work for the common case of RETURNING the value of a serial/sequence column, but gets confused when returning results out-of-order (CREATE TABLE x (a int, b int), INSERT ... RETURNING b, a) and doesn't let you specify the same column multiple times (INSERT ... RETURNING b, b). These will be addressed soon. Regards, Omar Kilani
Attachment
omar@tinysofa.org writes:
> Attached is a patch (by Gavin Sherry, fixed up to apply to 8.1 by me) that
> implements INSERT ... RETURNING functionality.
> It does work for the common case of RETURNING the value of a serial/sequence
> column, but gets confused when returning results out-of-order (CREATE TABLE x
> (a int, b int), INSERT ... RETURNING b, a) and doesn't let you specify the same
> column multiple times (INSERT ... RETURNING b, b). These will be addressed
> soon.
This is pretty considerably shy of what I thought had been agreed to
anyway:
- should allow expressions not only column names
- should work for UPDATE and DELETE too
regards, tom lane
Tom Lane wrote: > - should work for UPDATE and DELETE too And probably INSERT ... SELECT as well. -Neil
Are you still working on completing this? --------------------------------------------------------------------------- omar@tinysofa.org wrote: > Hi there, > > Attached is a patch (by Gavin Sherry, fixed up to apply to 8.1 by me) that > implements INSERT ... RETURNING functionality. > > It does work for the common case of RETURNING the value of a serial/sequence > column, but gets confused when returning results out-of-order (CREATE TABLE x > (a int, b int), INSERT ... RETURNING b, a) and doesn't let you specify the same > column multiple times (INSERT ... RETURNING b, b). These will be addressed > soon. > > Regards, > Omar Kilani [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Here here on this one. With the deprecation of oids on the horizon insert returning is to be extremely important. It's use with the uniqueidentifier mod would be really really helpful. On a similar note, is anyone working on the ability to have a column default as the product of a function on another column of the same row. I know this can be done as a trigger but something like CREATE TABLE abc (name text not null unique, hash not null default somehashfunc(name)); would be very convenient, and of course with the ability to get the product back with insert returning. Kevin McArthur ----- Original Message ----- From: "Bruce Momjian" <pgman@candle.pha.pa.us> To: <omar@tinysofa.org> Cc: <pgsql-patches@postgresql.org> Sent: Friday, July 29, 2005 7:26 PM Subject: Re: [PATCHES] INSERT ... RETURNING > > Are you still working on completing this? > > --------------------------------------------------------------------------- > > omar@tinysofa.org wrote: >> Hi there, >> >> Attached is a patch (by Gavin Sherry, fixed up to apply to 8.1 by me) >> that >> implements INSERT ... RETURNING functionality. >> >> It does work for the common case of RETURNING the value of a >> serial/sequence >> column, but gets confused when returning results out-of-order (CREATE >> TABLE x >> (a int, b int), INSERT ... RETURNING b, a) and doesn't let you specify >> the same >> column multiple times (INSERT ... RETURNING b, b). These will be >> addressed >> soon. >> >> Regards, >> Omar Kilani > > [ Attachment, skipping... ] > >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania > 19073 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
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 <> '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 <> 'Musical';
</programlisting>
</para>
<para>
+ Delete all films but comedies returning film titles using the
+ <literal>RETURNING</literal> clause:
+ <programlisting>
+ DELETE FROM films WHERE kind <> '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 } [, ...] ) |
<replaceableclass="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 < '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.xAS 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.xAS 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;
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 <> '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 <> 'Musical';
> </programlisting>
> </para>
>
> <para>
> + Delete all films but comedies returning film titles using the
> + <literal>RETURNING</literal> clause:
> + <programlisting>
> + DELETE FROM films WHERE kind <> '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 } [, ...] ) |
<replaceableclass="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 < '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.xAS 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.xAS 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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073