Implement UPDATE tab SET (col1,col2,...) = (SELECT ...), ...

This SQL-standard feature allows a sub-SELECT yielding multiple columns
(but only one row) to be used to compute the new values of several columns
to be updated.  While the same results can be had with an independent
sub-SELECT per column, such a workaround can require a great deal of
duplicated computation.

The standard actually says that the source for a multi-column assignment
could be any row-valued expression.  The implementation used here is
tightly tied to our existing sub-SELECT support and can't handle other
cases; the Bison grammar would have some issues with them too.  However,
I don't feel too bad about this since other cases can be converted into
sub-SELECTs.  For instance, "SET (a,b,c) = row_valued_function(x)" could
be written "SET (a,b,c) = (SELECT * FROM row_valued_function(x))".

2 files changed, 81 insertions, 14 deletions

diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 7f565be3c20..35b0699f086 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -24,7 +24,9 @@ PostgreSQL documentation
 [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
 UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
     SET { <replaceable class="PARAMETER">column_name</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
-          ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...]
+          ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) |
+          ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( <replaceable class="PARAMETER">sub-SELECT</replaceable> )
+        } [, ...]
     [ FROM <replaceable class="PARAMETER">from_list</replaceable> ]
     [ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
     [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
@@ -147,6 +149,21 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [
    </varlistentry>
 
    <varlistentry>
+    <term><replaceable class="PARAMETER">sub-SELECT</replaceable></term>
+    <listitem>
+     <para>
+      A <literal>SELECT</> sub-query that produces as many output columns
+      as are listed in the parenthesized column list preceding it.  The
+      sub-query must yield no more than one row when executed.  If it
+      yields one row, its column values are assigned to the target columns;
+      if it yields no rows, NULL values are assigned to the target columns.
+      The sub-query can refer to old values of the current row of the table
+      being updated.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><replaceable class="PARAMETER">from_list</replaceable></term>
     <listitem>
      <para>
@@ -325,6 +342,38 @@ UPDATE employees SET sales_count = sales_count + 1 WHERE id =
   </para>
 
   <para>
+   Update contact names in an accounts table to match the currently assigned
+   salesmen:
+<programlisting>
+UPDATE accounts SET (contact_first_name, contact_last_name) =
+    (SELECT first_name, last_name FROM salesmen
+     WHERE salesmen.id = accounts.sales_id);
+</programlisting>
+   A similar result could be accomplished with a join:
+<programlisting>
+UPDATE accounts SET contact_first_name = first_name,
+                    contact_last_name = last_name
+  FROM salesmen WHERE salesmen.id = accounts.sales_id;
+</programlisting>
+   However, the second query may give unexpected results
+   if <structname>salesmen</>.<structfield>id</> is not a unique key, whereas
+   the first query is guaranteed to raise an error if there are multiple
+   <structfield>id</> matches.  Also, if there is no match for a particular
+   <structname>accounts</>.<structfield>sales_id</> entry, the first query
+   will set the corresponding name fields to NULL, whereas the second query
+   will not update that row at all.
+  </para>
+
+  <para>
+   Update statistics in a summary table to match the current data:
+<programlisting>
+UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =
+    (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d
+     WHERE d.group_id = s.group_id);
+</programlisting>
+  </para>
+
+  <para>
    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:
@@ -362,24 +411,21 @@ UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;
   </para>
 
   <para>
-   According to the standard, the column-list syntax should allow a list
-   of columns to be assigned from a single row-valued expression, such
-   as a sub-select:
-<programlisting>
-UPDATE accounts SET (contact_last_name, contact_first_name) =
-    (SELECT last_name, first_name FROM salesmen
-     WHERE salesmen.id = accounts.sales_id);
-</programlisting>
-   This is not currently implemented &mdash; the source must be a list
-   of independent expressions.
-  </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
    <literal>FROM</>.  Be careful when porting applications that use this
    extension.
   </para>
+
+  <para>
+   According to the standard, the source value for a parenthesized sub-list of
+   column names can be any row-valued expression yielding the correct number
+   of columns.  <productname>PostgreSQL</productname> only allows the source
+   value to be a parenthesized list of expressions (a row constructor) or a
+   sub-<literal>SELECT</>.  An individual column's updated value can be
+   specified as <literal>DEFAULT</> in the row-constructor case, but not
+   inside a sub-<literal>SELECT</>.
+  </para>
  </refsect1>
 </refentry>
diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml
index 8e34fb0bf2b..66b3cc9bf2f 100644
--- a/doc/src/sgml/rules.sgml
+++ b/doc/src/sgml/rules.sgml
@@ -1164,6 +1164,27 @@ SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10;
     original one.
 </para>
 
+<caution>
+ <para>
+  In many cases, tasks that could be performed by rules
+  on <command>INSERT</>/<command>UPDATE</>/<command>DELETE</> are better done
+  with triggers.  Triggers are notationally a bit more complicated, but their
+  semantics are much simpler to understand.  Rules tend to have surprising
+  results when the original query contains volatile functions: volatile
+  functions may get executed more times than expected in the process of
+  carrying out the rules.
+ </para>
+
+ <para>
+  Also, there are some cases that are not supported by these types of rules at
+  all, notably including <literal>WITH</> clauses in the original query and
+  multiple-assignment sub-<literal>SELECT</>s in the <literal>SET</> list
+  of <command>UPDATE</> queries.  This is because copying these constructs
+  into a rule query would result in multiple evaluations of the sub-query,
+  contrary to the express intent of the query's author.
+ </para>
+</caution>
+
 <sect2>
 <title>How Update Rules Work</title>