1 files changed, 357 insertions, 0 deletions
diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out
new file mode 100644
index 0000000..cb51bb8
--- /dev/null
+++ b/src/test/regress/expected/returning.out
@@ -0,0 +1,357 @@
+--
+-- Test INSERT/UPDATE/DELETE RETURNING
+--
+-- Simple cases
+CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42);
+INSERT INTO foo (f2,f3)
+  VALUES ('test', DEFAULT), ('More', 11), (upper('more'), 7+9)
+  RETURNING *, f1+f3 AS sum;
+ f1 |  f2  | f3 | sum 
+----+------+----+-----
+  1 | test | 42 |  43
+  2 | More | 11 |  13
+  3 | MORE | 16 |  19
+(3 rows)
+
+SELECT * FROM foo;
+ f1 |  f2  | f3 
+----+------+----
+  1 | test | 42
+  2 | More | 11
+  3 | MORE | 16
+(3 rows)
+
+UPDATE foo SET f2 = lower(f2), f3 = DEFAULT RETURNING foo.*, f1+f3 AS sum13;
+ f1 |  f2  | f3 | sum13 
+----+------+----+-------
+  1 | test | 42 |    43
+  2 | more | 42 |    44
+  3 | more | 42 |    45
+(3 rows)
+
+SELECT * FROM foo;
+ f1 |  f2  | f3 
+----+------+----
+  1 | test | 42
+  2 | more | 42
+  3 | more | 42
+(3 rows)
+
+DELETE FROM foo WHERE f1 > 2 RETURNING f3, f2, f1, least(f1,f3);
+ f3 |  f2  | f1 | least 
+----+------+----+-------
+ 42 | more |  3 |     3
+(1 row)
+
+SELECT * FROM foo;
+ f1 |  f2  | f3 
+----+------+----
+  1 | test | 42
+  2 | more | 42
+(2 rows)
+
+-- Subplans and initplans in the RETURNING list
+INSERT INTO foo SELECT f1+10, f2, f3+99 FROM foo
+  RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan,
+    EXISTS(SELECT * FROM int4_tbl) AS initplan;
+ f1 |  f2  | f3  | subplan | initplan 
+----+------+-----+---------+----------
+ 11 | test | 141 | t       | t
+ 12 | more | 141 | f       | t
+(2 rows)
+
+UPDATE foo SET f3 = f3 * 2
+  WHERE f1 > 10
+  RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan,
+    EXISTS(SELECT * FROM int4_tbl) AS initplan;
+ f1 |  f2  | f3  | subplan | initplan 
+----+------+-----+---------+----------
+ 11 | test | 282 | t       | t
+ 12 | more | 282 | f       | t
+(2 rows)
+
+DELETE FROM foo
+  WHERE f1 > 10
+  RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan,
+    EXISTS(SELECT * FROM int4_tbl) AS initplan;
+ f1 |  f2  | f3  | subplan | initplan 
+----+------+-----+---------+----------
+ 11 | test | 282 | t       | t
+ 12 | more | 282 | f       | t
+(2 rows)
+
+-- Joins
+UPDATE foo SET f3 = f3*2
+  FROM int4_tbl i
+  WHERE foo.f1 + 123455 = i.f1
+  RETURNING foo.*, i.f1 as "i.f1";
+ f1 |  f2  | f3 |  i.f1  
+----+------+----+--------
+  1 | test | 84 | 123456
+(1 row)
+
+SELECT * FROM foo;
+ f1 |  f2  | f3 
+----+------+----
+  2 | more | 42
+  1 | test | 84
+(2 rows)
+
+DELETE FROM foo
+  USING int4_tbl i
+  WHERE foo.f1 + 123455 = i.f1
+  RETURNING foo.*, i.f1 as "i.f1";
+ f1 |  f2  | f3 |  i.f1  
+----+------+----+--------
+  1 | test | 84 | 123456
+(1 row)
+
+SELECT * FROM foo;
+ f1 |  f2  | f3 
+----+------+----
+  2 | more | 42
+(1 row)
+
+-- Check inheritance cases
+CREATE TEMP TABLE foochild (fc int) INHERITS (foo);
+INSERT INTO foochild VALUES(123,'child',999,-123);
+ALTER TABLE foo ADD COLUMN f4 int8 DEFAULT 99;
+SELECT * FROM foo;
+ f1  |  f2   | f3  | f4 
+-----+-------+-----+----
+   2 | more  |  42 | 99
+ 123 | child | 999 | 99
+(2 rows)
+
+SELECT * FROM foochild;
+ f1  |  f2   | f3  |  fc  | f4 
+-----+-------+-----+------+----
+ 123 | child | 999 | -123 | 99
+(1 row)
+
+UPDATE foo SET f4 = f4 + f3 WHERE f4 = 99 RETURNING *;
+ f1  |  f2   | f3  |  f4  
+-----+-------+-----+------
+   2 | more  |  42 |  141
+ 123 | child | 999 | 1098
+(2 rows)
+
+SELECT * FROM foo;
+ f1  |  f2   | f3  |  f4  
+-----+-------+-----+------
+   2 | more  |  42 |  141
+ 123 | child | 999 | 1098
+(2 rows)
+
+SELECT * FROM foochild;
+ f1  |  f2   | f3  |  fc  |  f4  
+-----+-------+-----+------+------
+ 123 | child | 999 | -123 | 1098
+(1 row)
+
+UPDATE foo SET f3 = f3*2
+  FROM int8_tbl i
+  WHERE foo.f1 = i.q2
+  RETURNING *;
+ f1  |  f2   |  f3  |  f4  |        q1        | q2  
+-----+-------+------+------+------------------+-----
+ 123 | child | 1998 | 1098 | 4567890123456789 | 123
+(1 row)
+
+SELECT * FROM foo;
+ f1  |  f2   |  f3  |  f4  
+-----+-------+------+------
+   2 | more  |   42 |  141
+ 123 | child | 1998 | 1098
+(2 rows)
+
+SELECT * FROM foochild;
+ f1  |  f2   |  f3  |  fc  |  f4  
+-----+-------+------+------+------
+ 123 | child | 1998 | -123 | 1098
+(1 row)
+
+DELETE FROM foo
+  USING int8_tbl i
+  WHERE foo.f1 = i.q2
+  RETURNING *;
+ f1  |  f2   |  f3  |  f4  |        q1        | q2  
+-----+-------+------+------+------------------+-----
+ 123 | child | 1998 | 1098 | 4567890123456789 | 123
+(1 row)
+
+SELECT * FROM foo;
+ f1 |  f2  | f3 | f4  
+----+------+----+-----
+  2 | more | 42 | 141
+(1 row)
+
+SELECT * FROM foochild;
+ f1 | f2 | f3 | fc | f4 
+----+----+----+----+----
+(0 rows)
+
+DROP TABLE foochild;
+-- Rules and views
+CREATE TEMP VIEW voo AS SELECT f1, f2 FROM foo;
+CREATE RULE voo_i AS ON INSERT TO voo DO INSTEAD
+  INSERT INTO foo VALUES(new.*, 57);
+INSERT INTO voo VALUES(11,'zit');
+-- fails:
+INSERT INTO voo VALUES(12,'zoo') RETURNING *, f1*2;
+ERROR:  cannot perform INSERT RETURNING on relation "voo"
+HINT:  You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.
+-- fails, incompatible list:
+CREATE OR REPLACE RULE voo_i AS ON INSERT TO voo DO INSTEAD
+  INSERT INTO foo VALUES(new.*, 57) RETURNING *;
+ERROR:  RETURNING list has too many entries
+CREATE OR REPLACE RULE voo_i AS ON INSERT TO voo DO INSTEAD
+  INSERT INTO foo VALUES(new.*, 57) RETURNING f1, f2;
+-- should still work
+INSERT INTO voo VALUES(13,'zit2');
+-- works now
+INSERT INTO voo VALUES(14,'zoo2') RETURNING *;
+ f1 |  f2  
+----+------
+ 14 | zoo2
+(1 row)
+
+SELECT * FROM foo;
+ f1 |  f2  | f3 | f4  
+----+------+----+-----
+  2 | more | 42 | 141
+ 11 | zit  | 57 |  99
+ 13 | zit2 | 57 |  99
+ 14 | zoo2 | 57 |  99
+(4 rows)
+
+SELECT * FROM voo;
+ f1 |  f2  
+----+------
+  2 | more
+ 11 | zit
+ 13 | zit2
+ 14 | zoo2
+(4 rows)
+
+CREATE OR REPLACE RULE voo_u AS ON UPDATE TO voo DO INSTEAD
+  UPDATE foo SET f1 = new.f1, f2 = new.f2 WHERE f1 = old.f1
+  RETURNING f1, f2;
+update voo set f1 = f1 + 1 where f2 = 'zoo2';
+update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2;
+ f1 |  f2  | ?column? 
+----+------+----------
+ 16 | zoo2 |       32
+(1 row)
+
+SELECT * FROM foo;
+ f1 |  f2  | f3 | f4  
+----+------+----+-----
+  2 | more | 42 | 141
+ 11 | zit  | 57 |  99
+ 13 | zit2 | 57 |  99
+ 16 | zoo2 | 57 |  99
+(4 rows)
+
+SELECT * FROM voo;
+ f1 |  f2  
+----+------
+  2 | more
+ 11 | zit
+ 13 | zit2
+ 16 | zoo2
+(4 rows)
+
+CREATE OR REPLACE RULE voo_d AS ON DELETE TO voo DO INSTEAD
+  DELETE FROM foo WHERE f1 = old.f1
+  RETURNING f1, f2;
+DELETE FROM foo WHERE f1 = 13;
+DELETE FROM foo WHERE f2 = 'zit' RETURNING *;
+ f1 | f2  | f3 | f4 
+----+-----+----+----
+ 11 | zit | 57 | 99
+(1 row)
+
+SELECT * FROM foo;
+ f1 |  f2  | f3 | f4  
+----+------+----+-----
+  2 | more | 42 | 141
+ 16 | zoo2 | 57 |  99
+(2 rows)
+
+SELECT * FROM voo;
+ f1 |  f2  
+----+------
+  2 | more
+ 16 | zoo2
+(2 rows)
+
+-- Try a join case
+CREATE TEMP TABLE joinme (f2j text, other int);
+INSERT INTO joinme VALUES('more', 12345);
+INSERT INTO joinme VALUES('zoo2', 54321);
+INSERT INTO joinme VALUES('other', 0);
+CREATE TEMP VIEW joinview AS
+  SELECT foo.*, other FROM foo JOIN joinme ON (f2 = f2j);
+SELECT * FROM joinview;
+ f1 |  f2  | f3 | f4  | other 
+----+------+----+-----+-------
+  2 | more | 42 | 141 | 12345
+ 16 | zoo2 | 57 |  99 | 54321
+(2 rows)
+
+CREATE RULE joinview_u AS ON UPDATE TO joinview DO INSTEAD
+  UPDATE foo SET f1 = new.f1, f3 = new.f3
+    FROM joinme WHERE f2 = f2j AND f2 = old.f2
+    RETURNING foo.*, other;
+UPDATE joinview SET f1 = f1 + 1 WHERE f3 = 57 RETURNING *, other + 1;
+ f1 |  f2  | f3 | f4 | other | ?column? 
+----+------+----+----+-------+----------
+ 17 | zoo2 | 57 | 99 | 54321 |    54322
+(1 row)
+
+SELECT * FROM joinview;
+ f1 |  f2  | f3 | f4  | other 
+----+------+----+-----+-------
+  2 | more | 42 | 141 | 12345
+ 17 | zoo2 | 57 |  99 | 54321
+(2 rows)
+
+SELECT * FROM foo;
+ f1 |  f2  | f3 | f4  
+----+------+----+-----
+  2 | more | 42 | 141
+ 17 | zoo2 | 57 |  99
+(2 rows)
+
+SELECT * FROM voo;
+ f1 |  f2  
+----+------
+  2 | more
+ 17 | zoo2
+(2 rows)
+
+-- Check aliased target relation
+INSERT INTO foo AS bar DEFAULT VALUES RETURNING *; -- ok
+ f1 | f2 | f3 | f4 
+----+----+----+----
+  4 |    | 42 | 99
+(1 row)
+
+INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*; -- fails, wrong name
+ERROR:  invalid reference to FROM-clause entry for table "foo"
+LINE 1: INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*;
+                                                        ^
+HINT:  Perhaps you meant to reference the table alias "bar".
+INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.*; -- ok
+ f1 | f2 | f3 | f4 
+----+----+----+----
+  5 |    | 42 | 99
+(1 row)
+
+INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.f3; -- ok
+ f3 
+----
+ 42
+(1 row)
+