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

4 files changed, 286 insertions, 3 deletions

diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out
new file mode 100644
index 00000000000..efe2ec0510e
--- /dev/null
+++ b/src/test/regress/expected/returning.out
@@ -0,0 +1,195 @@
+--
+-- Test INSERT/UPDATE/DELETE RETURNING
+--
+-- Simple cases
+CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42);
+NOTICE:  CREATE TABLE will create implicit sequence "foo_f1_seq" for serial column "foo.f1"
+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.q1
+  RETURNING *;
+ f1  |  f2   |  f3  |  f4  | q1  | q2  
+-----+-------+------+------+-----+-----
+ 123 | child | 1998 | 1098 | 123 | 456
+(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.q1
+  RETURNING *;
+ f1  |  f2   |  f3  |  f4  | q1  | q2  
+-----+-------+------+------+-----+-----
+ 123 | child | 1998 | 1098 | 123 | 456
+(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, foo;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 83f556a5a25..d675c07ff18 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -1,6 +1,6 @@
 # ----------
 # The first group of parallel test
-# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.33 2006/08/04 00:00:13 tgl Exp $
+# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.34 2006/08/12 02:52:06 tgl Exp $
 # ----------
 test: boolean char name varchar text int2 int4 int8 oid float4 float8 bit numeric
 
@@ -75,7 +75,7 @@ test: select_views portals_p2 rules foreign_key cluster dependency guc
 # The sixth group of parallel test
 # ----------
 # "plpgsql" cannot run concurrently with "rules"
-test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes
+test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning
 
 # run stats by itself because its delay may be insufficient under heavy load
 test: stats
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index ec051c04551..1bb8742da6c 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -1,4 +1,4 @@
-# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.31 2006/08/04 00:00:13 tgl Exp $
+# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.32 2006/08/12 02:52:06 tgl Exp $
 # This should probably be in an order similar to parallel_schedule.
 test: boolean
 test: char
@@ -100,5 +100,6 @@ test: alter_table
 test: sequence
 test: polymorphism
 test: rowtypes
+test: returning
 test: stats
 test: tablespace
diff --git a/src/test/regress/sql/returning.sql b/src/test/regress/sql/returning.sql
new file mode 100644
index 00000000000..a16ac63129e
--- /dev/null
+++ b/src/test/regress/sql/returning.sql
@@ -0,0 +1,87 @@
+--
+-- 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;
+
+SELECT * FROM foo;
+
+UPDATE foo SET f2 = lower(f2), f3 = DEFAULT RETURNING foo.*, f1+f3 AS sum13;
+
+SELECT * FROM foo;
+
+DELETE FROM foo WHERE f1 > 2 RETURNING f3, f2, f1, least(f1,f3);
+
+SELECT * FROM foo;
+
+-- 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;
+
+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;
+
+DELETE FROM foo
+  WHERE f1 > 10
+  RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan,
+    EXISTS(SELECT * FROM int4_tbl) AS initplan;
+
+-- Joins
+
+UPDATE foo SET f3 = f3*2
+  FROM int4_tbl i
+  WHERE foo.f1 + 123455 = i.f1
+  RETURNING foo.*, i.f1 as "i.f1";
+
+SELECT * FROM foo;
+
+DELETE FROM foo
+  USING int4_tbl i
+  WHERE foo.f1 + 123455 = i.f1
+  RETURNING foo.*, i.f1 as "i.f1";
+
+SELECT * FROM foo;
+
+-- 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;
+SELECT * FROM foochild;
+
+UPDATE foo SET f4 = f4 + f3 WHERE f4 = 99 RETURNING *;
+
+SELECT * FROM foo;
+SELECT * FROM foochild;
+
+UPDATE foo SET f3 = f3*2
+  FROM int8_tbl i
+  WHERE foo.f1 = i.q1
+  RETURNING *;
+
+SELECT * FROM foo;
+SELECT * FROM foochild;
+
+DELETE FROM foo
+  USING int8_tbl i
+  WHERE foo.f1 = i.q1
+  RETURNING *;
+
+SELECT * FROM foo;
+SELECT * FROM foochild;
+
+DROP TABLE foochild, foo;