Add INSERT/UPDATE/DELETE RETURNING, with basic docs and regression tests.

plpgsql support to come later.  Along the way, convert execMain's
SELECT INTO support into a DestReceiver, in order to eliminate some ugly
special cases.

Jonah Harris and Tom Lane

3 files changed, 164 insertions, 14 deletions

diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index b61e6cacd21..6acc01b5604 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.26 2006/01/22 05:20:33 neilc Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.27 2006/08/12 02:52:03 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -23,6 +23,7 @@ PostgreSQL documentation
 DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
     [ USING <replaceable class="PARAMETER">usinglist</replaceable> ]
     [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
+    [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -60,6 +61,15 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ]
   </para>
 
   <para>
+   The optional <literal>RETURNING</> clause causes <command>DELETE</>
+   to compute and return value(s) based on each row actually deleted.
+   Any expression using the table's columns, and/or columns of other
+   tables mentioned in <literal>USING</literal>, can be computed.
+   The syntax of the <literal>RETURNING</> list is identical to that of the
+   output list of <command>SELECT</>.
+  </para>
+
+  <para>
    You must have the <literal>DELETE</literal> privilege on the table
    to delete from it, as well as the <literal>SELECT</literal>
    privilege for any table in the <literal>USING</literal> clause or
@@ -130,6 +140,28 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ]
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="PARAMETER">output_expression</replaceable></term>
+    <listitem>
+     <para>
+      An expression to be computed and returned by the <command>DELETE</>
+      command after each row is deleted.  The expression may use any
+      column names of the <replaceable class="PARAMETER">table</replaceable>
+      or table(s) listed in <literal>USING</>.
+      Write <literal>*</> to return all columns.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="PARAMETER">output_name</replaceable></term>
+    <listitem>
+     <para>
+      A name to use for a returned column.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
  </refsect1>
 
@@ -148,6 +180,14 @@ DELETE <replaceable class="parameter">count</replaceable>
    class="parameter">condition</replaceable> (this is not considered
    an error).
   </para>
+
+  <para>
+   If the <command>DELETE</> command contains a <literal>RETURNING</>
+   clause, the result will be similar to that of a <command>SELECT</>
+   statement containing the columns and values defined in the
+   <literal>RETURNING</> list, computed over the row(s) deleted by the
+   command.
+  </para>
  </refsect1>
 
  <refsect1>
@@ -191,16 +231,22 @@ DELETE FROM films WHERE kind &lt;&gt; 'Musical';
 DELETE FROM films;
 </programlisting>      
   </para>
+
+  <para>
+   Delete completed tasks, returning full details of the deleted rows:
+<programlisting>
+DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
+</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.
+   This command conforms to the <acronym>SQL</acronym> standard, except
+   that the <literal>USING</literal> and <literal>RETURNING</> clauses
+   are <productname>PostgreSQL</productname> extensions.
   </para>
  </refsect1>
 </refentry>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 4e589b599b6..55eaef08523 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.30 2005/11/17 22:14:51 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.31 2006/08/12 02:52:03 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -21,7 +21,8 @@ PostgreSQL documentation
  <refsynopsisdiv>
 <synopsis>
 INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
-    { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) | <replaceable class="PARAMETER">query</replaceable> }
+    { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
+    [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -30,8 +31,8 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
 
   <para>
    <command>INSERT</command> inserts new rows into a table.
-   One can insert a single row specified by value expressions,
-   or several rows as a result of a query.
+   One can insert rows specified by value expressions,
+   or rows computed as a result of a query.
   </para>
 
   <para>
@@ -56,6 +57,16 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
   </para>
 
   <para>
+   The optional <literal>RETURNING</> clause causes <command>INSERT</>
+   to compute and return value(s) based on each row actually inserted.
+   This is primarily useful for obtaining values that were supplied by
+   defaults, such as a serial sequence number.  However, any expression
+   using the table's columns is allowed.  The syntax of the
+   <literal>RETURNING</> list is identical to that of the output list
+   of <command>SELECT</>.
+  </para>
+
+  <para>
    You must have <literal>INSERT</literal> privilege to a table in
    order to insert into it.  If you use the <replaceable
    class="PARAMETER">query</replaceable> clause to insert rows from a
@@ -123,11 +134,33 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
     <listitem>
      <para>
       A query (<command>SELECT</command> statement) that supplies the
-      rows to be inserted.  Refer to the <command>SELECT</command>
+      rows to be inserted.  Refer to the
+      <xref linkend="sql-select" endterm="sql-select-title">
       statement for a description of the syntax.
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="PARAMETER">output_expression</replaceable></term>
+    <listitem>
+     <para>
+      An expression to be computed and returned by the <command>INSERT</>
+      command after each row is inserted.  The expression may use any
+      column names of the <replaceable class="PARAMETER">table</replaceable>.
+      Write <literal>*</> to return all columns of the inserted row(s).
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="PARAMETER">output_name</replaceable></term>
+    <listitem>
+     <para>
+      A name to use for a returned column.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
  </refsect1>
 
@@ -147,6 +180,14 @@ INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</repl
    <acronym>OID</acronym> assigned to the inserted row.  Otherwise
    <replaceable class="parameter">oid</replaceable> is zero.
   </para>
+
+  <para>
+   If the <command>INSERT</> command contains a <literal>RETURNING</>
+   clause, the result will be similar to that of a <command>SELECT</>
+   statement containing the columns and values defined in the
+   <literal>RETURNING</> list, computed over the row(s) inserted by the
+   command.
+  </para>
  </refsect1>
 
  <refsect1>
@@ -213,13 +254,25 @@ INSERT INTO tictactoe (game, board)
     VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');
 </programlisting>
   </para>
+
+  <para>
+   Insert a single row into table <literal>distributors</literal>, returning
+   the sequence number generated by the <literal>DEFAULT</literal> clause:
+
+<programlisting>
+INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
+   RETURNING did;
+</programlisting>
+  </para>
  </refsect1>
 
  <refsect1>
   <title>Compatibility</title>
 
   <para>
-   <command>INSERT</command> conforms to the SQL standard.  The case in
+   <command>INSERT</command> conforms to the SQL standard, except that
+   the <literal>RETURNING</> clause is a
+   <productname>PostgreSQL</productname> extension.  Also, 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.
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 3b03e86a1eb..5d1265e945f 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.37 2006/03/08 22:59:09 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.38 2006/08/12 02:52:03 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -24,6 +24,7 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
     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">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -53,6 +54,16 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
   </para>
 
   <para>
+   The optional <literal>RETURNING</> clause causes <command>UPDATE</>
+   to compute and return value(s) based on each row actually updated.
+   Any expression using the table's columns, and/or columns of other
+   tables mentioned in <literal>FROM</literal>, can be computed.
+   The new (post-update) values of the table's columns are used.
+   The syntax of the <literal>RETURNING</> list is identical to that of the
+   output list of <command>SELECT</>.
+  </para>
+
+  <para>
    You must have the <literal>UPDATE</literal> privilege on the table
    to update it, as well as the <literal>SELECT</literal>
    privilege to any table whose values are read in the
@@ -147,6 +158,28 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="PARAMETER">output_expression</replaceable></term>
+    <listitem>
+     <para>
+      An expression to be computed and returned by the <command>UPDATE</>
+      command after each row is updated.  The expression may use any
+      column names of the <replaceable class="PARAMETER">table</replaceable>
+      or table(s) listed in <literal>FROM</>.
+      Write <literal>*</> to return all columns.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="PARAMETER">output_name</replaceable></term>
+    <listitem>
+     <para>
+      A name to use for a returned column.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
  </refsect1>
 
@@ -165,6 +198,14 @@ UPDATE <replaceable class="parameter">count</replaceable>
    class="parameter">condition</replaceable> (this is not considered
    an error).
   </para>
+
+  <para>
+   If the <command>UPDATE</> command contains a <literal>RETURNING</>
+   clause, the result will be similar to that of a <command>SELECT</>
+   statement containing the columns and values defined in the
+   <literal>RETURNING</> list, computed over the row(s) updated by the
+   command.
+  </para>
  </refsect1>
 
  <refsect1>
@@ -213,6 +254,16 @@ UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
   </para>
 
   <para>
+   Perform the same operation and return the updated entries:
+
+<programlisting>
+UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
+  WHERE city = 'San Francisco' AND date = '2003-07-03'
+  RETURNING temp_lo, temp_hi, prcp;
+</programlisting>
+  </para>
+
+  <para>
    Increment the sales count of the salesperson who manages the
    account for Acme Corporation, using the <literal>FROM</literal>
    clause syntax:
@@ -256,8 +307,8 @@ COMMIT;
 
   <para>
    This command conforms to the <acronym>SQL</acronym> standard, except
-   that the <literal>FROM</literal> clause is a
-   <productname>PostgreSQL</productname> extension.
+   that the <literal>FROM</literal> and <literal>RETURNING</> clauses
+   are <productname>PostgreSQL</productname> extensions.
   </para>
 
   <para>