diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/update.out | 77 | ||||
| -rw-r--r-- | src/test/regress/sql/update.sql | 22 |
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 |