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, 83 insertions, 16 deletions

diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index 71b856f95c6..1de2a867a85 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -55,31 +55,80 @@ SELECT * FROM update_test;
 --
 -- Test multiple-set-clause syntax
 --
+INSERT INTO update_test SELECT a,b+1,c FROM update_test;
+SELECT * FROM update_test;
+  a  | b  |  c  
+-----+----+-----
+ 100 | 20 | foo
+ 100 | 20 | 
+ 100 | 21 | foo
+ 100 | 21 | 
+(4 rows)
+
 UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
 SELECT * FROM update_test;
   a  | b  |   c   
 -----+----+-------
  100 | 20 | 
+ 100 | 21 | 
   10 | 31 | bugle
-(2 rows)
+  10 | 32 | bugle
+(4 rows)
 
 UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
 SELECT * FROM update_test;
   a  | b  |  c  
 -----+----+-----
  100 | 20 | 
+ 100 | 21 | 
   11 | 41 | car
-(2 rows)
+  11 | 42 | car
+(4 rows)
 
 -- fail, multi assignment to same column:
 UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
 ERROR:  multiple assignments to same column "b"
--- XXX this should work, but doesn't yet:
-UPDATE update_test SET (a,b) = (select a,b FROM update_test where c = 'foo')
-  WHERE a = 10;
-ERROR:  syntax error at or near "select"
-LINE 1: UPDATE update_test SET (a,b) = (select a,b FROM update_test ...
-                                        ^
+-- uncorrelated sub-select:
+UPDATE update_test
+  SET (b,a) = (select a,b from update_test where b = 41 and c = 'car')
+  WHERE a = 100 AND b = 20;
+SELECT * FROM update_test;
+  a  | b  |  c  
+-----+----+-----
+ 100 | 21 | 
+  11 | 41 | car
+  11 | 42 | car
+  41 | 11 | 
+(4 rows)
+
+-- correlated sub-select:
+UPDATE update_test o
+  SET (b,a) = (select a+1,b from update_test i
+               where i.a=o.a and i.b=o.b and i.c is not distinct from o.c);
+SELECT * FROM update_test;
+ a  |  b  |  c  
+----+-----+-----
+ 21 | 101 | 
+ 41 |  12 | car
+ 42 |  12 | car
+ 11 |  42 | 
+(4 rows)
+
+-- fail, multiple rows supplied:
+UPDATE update_test SET (b,a) = (select a+1,b from update_test);
+ERROR:  more than one row returned by a subquery used as an expression
+-- set to null if no rows supplied:
+UPDATE update_test SET (b,a) = (select a+1,b from update_test where a = 1000)
+  WHERE a = 11;
+SELECT * FROM update_test;
+ a  |  b  |  c  
+----+-----+-----
+ 21 | 101 | 
+ 41 |  12 | car
+ 42 |  12 | car
+    |     | 
+(4 rows)
+
 -- if an alias for the target table is specified, don't allow references
 -- to the original table name
 UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
@@ -90,10 +139,12 @@ HINT:  Perhaps you meant to reference the table alias "t".
 -- Make sure that we can update to a TOASTed value.
 UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car';
 SELECT a, b, char_length(c) FROM update_test;
-  a  | b  | char_length 
------+----+-------------
- 100 | 20 |            
-  11 | 41 |       10000
-(2 rows)
+ a  |  b  | char_length 
+----+-----+-------------
+ 21 | 101 |            
+    |     |            
+ 41 |  12 |       10000
+ 42 |  12 |       10000
+(4 rows)
 
 DROP TABLE update_test;
diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql
index a8a028f7101..e71128c04dd 100644
--- a/src/test/regress/sql/update.sql
+++ b/src/test/regress/sql/update.sql
@@ -39,6 +39,9 @@ SELECT * FROM update_test;
 -- Test multiple-set-clause syntax
 --
 
+INSERT INTO update_test SELECT a,b+1,c FROM update_test;
+SELECT * FROM update_test;
+
 UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
 SELECT * FROM update_test;
 UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
@@ -46,9 +49,22 @@ SELECT * FROM update_test;
 -- fail, multi assignment to same column:
 UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
 
--- XXX this should work, but doesn't yet:
-UPDATE update_test SET (a,b) = (select a,b FROM update_test where c = 'foo')
-  WHERE a = 10;
+-- uncorrelated sub-select:
+UPDATE update_test
+  SET (b,a) = (select a,b from update_test where b = 41 and c = 'car')
+  WHERE a = 100 AND b = 20;
+SELECT * FROM update_test;
+-- correlated sub-select:
+UPDATE update_test o
+  SET (b,a) = (select a+1,b from update_test i
+               where i.a=o.a and i.b=o.b and i.c is not distinct from o.c);
+SELECT * FROM update_test;
+-- fail, multiple rows supplied:
+UPDATE update_test SET (b,a) = (select a+1,b from update_test);
+-- set to null if no rows supplied:
+UPDATE update_test SET (b,a) = (select a+1,b from update_test where a = 1000)
+  WHERE a = 11;
+SELECT * FROM update_test;
 
 -- if an alias for the target table is specified, don't allow references
 -- to the original table name