diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
new file mode 100644
index 58a603a..b479d98
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -4936,12 +4936,12 @@ INSERT INTO ft2 (c1,c2,c3) SELECT c1+100
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
INSERT INTO ft2 (c1,c2,c3)
- VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *;
- c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
-------+-----+-----+----+----+----+------------+----
- 1101 | 201 | aaa | | | | ft2 |
- 1102 | 202 | bbb | | | | ft2 |
- 1103 | 203 | ccc | | | | ft2 |
+ VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING old.*, new.*;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+----+----+----+----+----+----+----+----+------+-----+-----+----+----+----+------------+----
+ | | | | | | | | 1101 | 201 | aaa | | | | ft2 |
+ | | | | | | | | 1102 | 202 | bbb | | | | ft2 |
+ | | | | | | | | 1103 | 203 | ccc | | | | ft2 |
(3 rows)
INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee');
@@ -5072,6 +5072,31 @@ UPDATE ft2 SET c2 = c2 + 400, c3 = c3 ||
1017 | 507 | 0001700017_update7 | | | | ft2 |
(102 rows)
+BEGIN;
+ EXPLAIN (verbose, costs off)
+ UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7b' WHERE c1 % 10 = 7 AND c1 < 40
+ RETURNING old.*, new.*; -- can't be pushed down
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.ft2
+ Output: old.c1, old.c2, old.c3, old.c4, old.c5, old.c6, old.c7, old.c8, new.c1, new.c2, new.c3, new.c4, new.c5, new.c6, new.c7, new.c8
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c3 = $3 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
+ -> Foreign Scan on public.ft2
+ Output: (c2 + 400), (c3 || '_update7b'::text), ctid, ft2.*
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" < 40)) AND ((("C 1" % 10) = 7)) FOR UPDATE
+(6 rows)
+
+ UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7b' WHERE c1 % 10 = 7 AND c1 < 40
+ RETURNING old.*, new.*;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+----+-----+---------------+------------------------------+--------------------------+----+------------+-----+----+-----+------------------------+------------------------------+--------------------------+----+------------+-----
+ 7 | 407 | 00007_update7 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo | 7 | 807 | 00007_update7_update7b | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo
+ 17 | 407 | 00017_update7 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo | 17 | 807 | 00017_update7_update7b | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo
+ 27 | 407 | 00027_update7 | Wed Jan 28 00:00:00 1970 PST | Wed Jan 28 00:00:00 1970 | 7 | 7 | foo | 27 | 807 | 00027_update7_update7b | Wed Jan 28 00:00:00 1970 PST | Wed Jan 28 00:00:00 1970 | 7 | 7 | foo
+ 37 | 407 | 00037_update7 | Sat Feb 07 00:00:00 1970 PST | Sat Feb 07 00:00:00 1970 | 7 | 7 | foo | 37 | 807 | 00037_update7_update7b | Sat Feb 07 00:00:00 1970 PST | Sat Feb 07 00:00:00 1970 | 7 | 7 | foo
+(4 rows)
+
+ROLLBACK;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can be pushed down
@@ -5202,6 +5227,29 @@ DELETE FROM ft2 WHERE c1 % 10 = 5 RETURN
1105 |
(103 rows)
+BEGIN;
+ EXPLAIN (verbose, costs off)
+ DELETE FROM ft2 WHERE c1 % 10 = 6 AND c1 < 40 RETURNING old.c1, c4; -- can't be pushed down
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ Delete on public.ft2
+ Output: old.c1, ft2.c4
+ Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1 RETURNING "C 1", c4
+ -> Foreign Scan on public.ft2
+ Output: ctid
+ Remote SQL: SELECT ctid FROM "S 1"."T 1" WHERE (("C 1" < 40)) AND ((("C 1" % 10) = 6)) FOR UPDATE
+(6 rows)
+
+ DELETE FROM ft2 WHERE c1 % 10 = 6 AND c1 < 40 RETURNING old.c1, c4;
+ c1 | c4
+----+------------------------------
+ 6 | Wed Jan 07 00:00:00 1970 PST
+ 16 | Sat Jan 17 00:00:00 1970 PST
+ 26 | Tue Jan 27 00:00:00 1970 PST
+ 36 | Fri Feb 06 00:00:00 1970 PST
+(4 rows)
+
+ROLLBACK;
EXPLAIN (verbose, costs off)
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can be pushed down
QUERY PLAN
@@ -6126,6 +6174,70 @@ UPDATE ft2 SET c3 = 'foo'
(1296,96,foo,,,,"ft2 ",) | 1296 | 96 | foo | | | | ft2 | | (96,97,AAA096) | 96 | 97 | AAA096
(16 rows)
+BEGIN;
+ EXPLAIN (verbose, costs off)
+ UPDATE ft2 SET c3 = 'bar'
+ FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
+ WHERE ft2.c1 > 1200 AND ft2.c2 = ft4.c1
+ RETURNING old, new, ft2, ft2.*, ft4, ft4.*; -- can't be pushed down
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.ft2
+ Output: old.*, new.*, ft2.*, ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.*, ft4.c1, ft4.c2, ft4.c3
+ Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
+ -> Foreign Scan
+ Output: 'bar'::text, ft2.ctid, ft2.*, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3
+ Relations: ((public.ft2) INNER JOIN (public.ft4)) INNER JOIN (public.ft5)
+ Remote SQL: SELECT r1.ctid, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END, r2.c1, r2.c2, r2.c3 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1.c2 = r2.c1)) AND ((r1."C 1" > 1200)))) INNER JOIN "S 1"."T 4" r3 ON (((r2.c1 = r3.c1)))) FOR UPDATE OF r1
+ -> Nested Loop
+ Output: ft2.ctid, ft2.*, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3
+ Join Filter: (ft4.c1 = ft5.c1)
+ -> Sort
+ Output: ft2.ctid, ft2.*, ft2.c2, ft4.*, ft4.c1, ft4.c2, ft4.c3
+ Sort Key: ft2.c2
+ -> Hash Join
+ Output: ft2.ctid, ft2.*, ft2.c2, ft4.*, ft4.c1, ft4.c2, ft4.c3
+ Hash Cond: (ft2.c2 = ft4.c1)
+ -> Foreign Scan on public.ft2
+ Output: ft2.ctid, ft2.*, ft2.c2
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 1200)) FOR UPDATE
+ -> Hash
+ Output: ft4.*, ft4.c1, ft4.c2, ft4.c3
+ -> Foreign Scan on public.ft4
+ Output: ft4.*, ft4.c1, ft4.c2, ft4.c3
+ Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
+ -> Materialize
+ Output: ft5.*, ft5.c1
+ -> Foreign Scan on public.ft5
+ Output: ft5.*, ft5.c1
+ Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4"
+(29 rows)
+
+ UPDATE ft2 SET c3 = 'bar'
+ FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
+ WHERE ft2.c1 > 1200 AND ft2.c2 = ft4.c1
+ RETURNING old, new, ft2, ft2.*, ft4, ft4.*;
+ old | new | ft2 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | ft4 | c1 | c2 | c3
+--------------------------------+--------------------------------+--------------------------------+------+----+-----+----+----+----+------------+----+----------------+----+----+--------
+ (1206,6,foo,,,,"ft2 ",) | (1206,6,bar,,,,"ft2 ",) | (1206,6,bar,,,,"ft2 ",) | 1206 | 6 | bar | | | | ft2 | | (6,7,AAA006) | 6 | 7 | AAA006
+ (1212,12,foo,,,,"ft2 ",) | (1212,12,bar,,,,"ft2 ",) | (1212,12,bar,,,,"ft2 ",) | 1212 | 12 | bar | | | | ft2 | | (12,13,AAA012) | 12 | 13 | AAA012
+ (1224,24,foo,,,,"ft2 ",) | (1224,24,bar,,,,"ft2 ",) | (1224,24,bar,,,,"ft2 ",) | 1224 | 24 | bar | | | | ft2 | | (24,25,AAA024) | 24 | 25 | AAA024
+ (1230,30,foo,,,,"ft2 ",) | (1230,30,bar,,,,"ft2 ",) | (1230,30,bar,,,,"ft2 ",) | 1230 | 30 | bar | | | | ft2 | | (30,31,AAA030) | 30 | 31 | AAA030
+ (1242,42,foo,,,,"ft2 ",) | (1242,42,bar,,,,"ft2 ",) | (1242,42,bar,,,,"ft2 ",) | 1242 | 42 | bar | | | | ft2 | | (42,43,AAA042) | 42 | 43 | AAA042
+ (1248,48,foo,,,,"ft2 ",) | (1248,48,bar,,,,"ft2 ",) | (1248,48,bar,,,,"ft2 ",) | 1248 | 48 | bar | | | | ft2 | | (48,49,AAA048) | 48 | 49 | AAA048
+ (1260,60,foo,,,,"ft2 ",) | (1260,60,bar,,,,"ft2 ",) | (1260,60,bar,,,,"ft2 ",) | 1260 | 60 | bar | | | | ft2 | | (60,61,AAA060) | 60 | 61 | AAA060
+ (1266,66,foo,,,,"ft2 ",) | (1266,66,bar,,,,"ft2 ",) | (1266,66,bar,,,,"ft2 ",) | 1266 | 66 | bar | | | | ft2 | | (66,67,AAA066) | 66 | 67 | AAA066
+ (1278,78,foo,,,,"ft2 ",) | (1278,78,bar,,,,"ft2 ",) | (1278,78,bar,,,,"ft2 ",) | 1278 | 78 | bar | | | | ft2 | | (78,79,AAA078) | 78 | 79 | AAA078
+ (1284,84,foo,,,,"ft2 ",) | (1284,84,bar,,,,"ft2 ",) | (1284,84,bar,,,,"ft2 ",) | 1284 | 84 | bar | | | | ft2 | | (84,85,AAA084) | 84 | 85 | AAA084
+ (1296,96,foo,,,,"ft2 ",) | (1296,96,bar,,,,"ft2 ",) | (1296,96,bar,,,,"ft2 ",) | 1296 | 96 | bar | | | | ft2 | | (96,97,AAA096) | 96 | 97 | AAA096
+ (1218,18,foo,,,,"ft2 ",) | (1218,18,bar,,,,"ft2 ",) | (1218,18,bar,,,,"ft2 ",) | 1218 | 18 | bar | | | | ft2 | | (18,19,AAA018) | 18 | 19 | AAA018
+ (1236,36,foo,,,,"ft2 ",) | (1236,36,bar,,,,"ft2 ",) | (1236,36,bar,,,,"ft2 ",) | 1236 | 36 | bar | | | | ft2 | | (36,37,AAA036) | 36 | 37 | AAA036
+ (1254,54,foo,,,,"ft2 ",) | (1254,54,bar,,,,"ft2 ",) | (1254,54,bar,,,,"ft2 ",) | 1254 | 54 | bar | | | | ft2 | | (54,55,AAA054) | 54 | 55 | AAA054
+ (1272,72,foo,,,,"ft2 ",) | (1272,72,bar,,,,"ft2 ",) | (1272,72,bar,,,,"ft2 ",) | 1272 | 72 | bar | | | | ft2 | | (72,73,AAA072) | 72 | 73 | AAA072
+ (1290,90,foo,,,,"ft2 ",) | (1290,90,bar,,,,"ft2 ",) | (1290,90,bar,,,,"ft2 ",) | 1290 | 90 | bar | | | | ft2 | | (90,91,AAA090) | 90 | 91 | AAA090
+(16 rows)
+
+ ROLLBACK;
EXPLAIN (verbose, costs off)
DELETE FROM ft2
USING ft4 LEFT JOIN ft5 ON (ft4.c1 = ft5.c1)
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
new file mode 100644
index e3d147d..a4adbe1
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1456,7 +1456,7 @@ EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
INSERT INTO ft2 (c1,c2,c3)
- VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *;
+ VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING old.*, new.*;
INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee');
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; -- can be pushed down
@@ -1464,6 +1464,13 @@ UPDATE ft2 SET c2 = c2 + 300, c3 = c3 ||
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; -- can be pushed down
UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *;
+BEGIN;
+ EXPLAIN (verbose, costs off)
+ UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7b' WHERE c1 % 10 = 7 AND c1 < 40
+ RETURNING old.*, new.*; -- can't be pushed down
+ UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7b' WHERE c1 % 10 = 7 AND c1 < 40
+ RETURNING old.*, new.*;
+ROLLBACK;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can be pushed down
@@ -1472,6 +1479,11 @@ UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = f
EXPLAIN (verbose, costs off)
DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; -- can be pushed down
DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
+BEGIN;
+ EXPLAIN (verbose, costs off)
+ DELETE FROM ft2 WHERE c1 % 10 = 6 AND c1 < 40 RETURNING old.c1, c4; -- can't be pushed down
+ DELETE FROM ft2 WHERE c1 % 10 = 6 AND c1 < 40 RETURNING old.c1, c4;
+ROLLBACK;
EXPLAIN (verbose, costs off)
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can be pushed down
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
@@ -1498,6 +1510,17 @@ UPDATE ft2 SET c3 = 'foo'
FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
WHERE ft2.c1 > 1200 AND ft2.c2 = ft4.c1
RETURNING ft2, ft2.*, ft4, ft4.*;
+BEGIN;
+ EXPLAIN (verbose, costs off)
+ UPDATE ft2 SET c3 = 'bar'
+ FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
+ WHERE ft2.c1 > 1200 AND ft2.c2 = ft4.c1
+ RETURNING old, new, ft2, ft2.*, ft4, ft4.*; -- can't be pushed down
+ UPDATE ft2 SET c3 = 'bar'
+ FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
+ WHERE ft2.c1 > 1200 AND ft2.c2 = ft4.c1
+ RETURNING old, new, ft2, ft2.*, ft4, ft4.*;
+ ROLLBACK;
EXPLAIN (verbose, costs off)
DELETE FROM ft2
USING ft4 LEFT JOIN ft5 ON (ft4.c1 = ft5.c1)
diff --git a/doc/src/sgml/dml.sgml b/doc/src/sgml/dml.sgml
new file mode 100644
index cbbc5e2..09ba384
--- a/doc/src/sgml/dml.sgml
+++ b/doc/src/sgml/dml.sgml
@@ -303,7 +303,8 @@ DELETE FROM products;
- In an INSERT, the data available to RETURNING is
+ In an INSERT, the default data available to
+ RETURNING is
the row as it was inserted. This is not so useful in trivial inserts,
since it would just repeat the data provided by the client. But it can
be very handy when relying on computed default values. For example,
@@ -320,7 +321,8 @@ INSERT INTO users (firstname, lastname)
- In an UPDATE, the data available to RETURNING is
+ In an UPDATE, the default data available to
+ RETURNING is
the new content of the modified row. For example:
UPDATE products SET price = price * 1.10
@@ -330,7 +332,8 @@ UPDATE products SET price = price * 1.10
- In a DELETE, the data available to RETURNING is
+ In a DELETE, the default data available to
+ RETURNING is
the content of the deleted row. For example:
DELETE FROM products
@@ -340,6 +343,30 @@ DELETE FROM products
+ In each of these commands, it is also possible to explicitly return the
+ old and new content of the modified row. For example:
+
+UPDATE products SET price = price * 1.10
+ WHERE price <= 99.99
+ RETURNING name, old.price AS old_price, new.price AS new_price,
+ new.price - old.price AS price_increase;
+
+ In this example, writing new.price is the same as
+ writing price, but it makes the meaning clearer.
+
+
+
+ This syntax for returning old and new values is available in
+ INSERT, UPDATE, and
+ DELETE commands, but typically old values will be
+ NULL for an INSERT, and new values
+ will be NULL for a DELETE. However,
+ it can come in handy for an INSERT with an
+ ON CONFLICT DO UPDATE
+ clause, or a table that has rules.
+
+
+
If there are triggers () on the target table,
the data available to RETURNING is the row as modified by
the triggers. Thus, inspecting columns computed by triggers is another
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
new file mode 100644
index 1b81b4e..d84124c
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -25,7 +25,8 @@ PostgreSQL documentation
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
[ USING from_item [, ...] ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
- [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
+ [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
+ * | output_expression [ [ AS ] output_name ] [, ...] ]
@@ -159,6 +160,35 @@ DELETE FROM [ ONLY ]
+
+
+ output_alias
+
+
+ An optional substitute name for OLD or
+ NEW rows in the RETURNING list.
+
+
+
+ By default, old values from the target table can be returned by writing
+ OLD.column_name
+ or OLD.*, and new values can be returned by writing
+ NEW.column_name
+ or NEW.*. When an alias is provided, these names are
+ hidden and the old or new rows must be referred to using the alias.
+ For example RETURNING WITH (NEW AS n) n.*. An
+ unqualified column name or * causes old values to be
+ returned.
+
+
+
+ For a simple DELETE, all new values will be
+ NULL. However, if an ON DELETE
+ rule causes an INSERT or UPDATE
+ to be executed instead, the new values may be non-NULL.
+
+
+
output_expression
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
new file mode 100644
index 7cea703..d414771
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -26,7 +26,8 @@ INSERT INTO expression | DEFAULT } [, ...] ) [, ...] | query }
[ ON CONFLICT [ conflict_target ] conflict_action ]
- [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
+ [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
+ * | output_expression [ [ AS ] output_name ] [, ...] ]
where conflict_target can be one of:
@@ -294,6 +295,35 @@ INSERT INTO output_alias
+
+
+ An optional substitute name for OLD or
+ NEW rows in the RETURNING list.
+
+
+
+ By default, old values from the target table can be returned by writing
+ OLD.column_name
+ or OLD.*, and new values can be returned by writing
+ NEW.column_name
+ or NEW.*. When an alias is provided, these names are
+ hidden and the old or new rows must be referred to using the alias.
+ For example RETURNING WITH (OLD AS o) o.*. An
+ unqualified column name or * causes new values to be
+ returned.
+
+
+
+ For a simple INSERT, all old values will be
+ NULL. However, for an INSERT
+ with an ON CONFLICT DO UPDATE clause, the old
+ values may be non-NULL.
+
+
+
+
+
output_expression
@@ -714,6 +744,20 @@ INSERT INTO distributors (did, dname)
+ Insert or update new distributors as above, returning information
+ about any existing values that were updated, together with the new data
+ inserted. Note that the returned values for old_did
+ and old_dname will be NULL for
+ non-conflicting rows:
+
+INSERT INTO distributors (did, dname)
+ VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
+ ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname
+ RETURNING old.did AS old_did, old.dname AS old_dname,
+ new.did AS new_did, new.dname AS new_dname;
+
+
+
Insert a distributor, or do nothing for rows proposed for insertion
when an existing, excluded row (a row with a matching constrained
column or columns after before row insert triggers fire) exists.
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
new file mode 100644
index 2ab24b0..03e0546
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -29,7 +29,8 @@ UPDATE [ ONLY ] from_item [, ...] ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
- [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
+ [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
+ * | output_expression [ [ AS ] output_name ] [, ...] ]
@@ -212,6 +213,28 @@ UPDATE [ ONLY ] output_alias
+
+
+ An optional substitute name for OLD or
+ NEW rows in the RETURNING list.
+
+
+
+ By default, old values from the target table can be returned by writing
+ OLD.column_name
+ or OLD.*, and new values can be returned by writing
+ NEW.column_name
+ or NEW.*. When an alias is provided, these names are
+ hidden and the old or new rows must be referred to using the alias.
+ For example RETURNING WITH (OLD AS o) o.*. An
+ unqualified column name or * causes new values to be
+ returned.
+
+
+
+
+
output_expression
@@ -348,12 +371,13 @@ UPDATE weather SET temp_lo = temp_lo+1,
- Perform the same operation and return the updated entries:
+ Perform the same operation and return the updated entries, and the old
+ precipitation value:
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
WHERE city = 'San Francisco' AND date = '2003-07-03'
- RETURNING temp_lo, temp_hi, prcp;
+ RETURNING temp_lo, temp_hi, prcp, old.prcp AS old_prcp;
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
new file mode 100644
index a9d5056..92d617f
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -23,6 +23,7 @@
#include "nodes/extensible.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "optimizer/optimizer.h"
#include "parser/analyze.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteHandler.h"
@@ -2351,6 +2352,15 @@ show_plan_tlist(PlanState *planstate, Li
ancestors);
useprefix = list_length(es->rtable) > 1;
+ /*
+ * For ModifyTable with a RETURNING list that returns OLD/NEW Vars, prefix
+ * all Vars in the output so that we can tell them apart.
+ */
+ if (!useprefix &&
+ IsA(plan, ModifyTable) &&
+ contain_vars_returning_old_or_new((Node *) ((ModifyTable *) plan)->returningLists))
+ useprefix = true;
+
/* Deparse each result column (we now include resjunk ones) */
foreach(lc, plan->targetlist)
{
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
new file mode 100644
index ffd3ca4..bae8456
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -54,10 +54,15 @@
typedef struct ExprSetupInfo
{
- /* Highest attribute numbers fetched from inner/outer/scan tuple slots: */
+ /*
+ * Highest attribute numbers fetched from inner/outer/scan/old/new tuple
+ * slots:
+ */
AttrNumber last_inner;
AttrNumber last_outer;
AttrNumber last_scan;
+ AttrNumber last_old;
+ AttrNumber last_new;
/* MULTIEXPR SubPlan nodes appearing in the expression: */
List *multiexpr_subplans;
} ExprSetupInfo;
@@ -435,8 +440,29 @@ ExecBuildProjectionInfo(List *targetList
/* INDEX_VAR is handled by default case */
default:
- /* get the tuple from the relation being scanned */
- scratch.opcode = EEOP_ASSIGN_SCAN_VAR;
+
+ /*
+ * Get the tuple from the relation being scanned.
+ *
+ * In a RETURNING clause, this defaults to the new version
+ * of the tuple when doing an INSERT or UPDATE, and the
+ * old tuple when doing a DELETE, but that may be
+ * overridden by explicitly referring to OLD/NEW.
+ */
+ switch (variable->varreturningtype)
+ {
+ case VAR_RETURNING_OLD:
+ scratch.opcode = EEOP_ASSIGN_OLD_VAR;
+ state->flags |= EEO_FLAG_HAS_OLD;
+ break;
+ case VAR_RETURNING_NEW:
+ scratch.opcode = EEOP_ASSIGN_NEW_VAR;
+ state->flags |= EEO_FLAG_HAS_NEW;
+ break;
+ default:
+ scratch.opcode = EEOP_ASSIGN_SCAN_VAR;
+ break;
+ }
break;
}
@@ -524,7 +550,7 @@ ExecBuildUpdateProjection(List *targetLi
int nAssignableCols;
bool sawJunk;
Bitmapset *assignedCols;
- ExprSetupInfo deform = {0, 0, 0, NIL};
+ ExprSetupInfo deform = {0, 0, 0, 0, 0, NIL};
ExprEvalStep scratch = {0};
int outerattnum;
ListCell *lc,
@@ -925,7 +951,18 @@ ExecInitExprRec(Expr *node, ExprState *s
/* INDEX_VAR is handled by default case */
default:
- scratch.opcode = EEOP_SCAN_SYSVAR;
+ switch (variable->varreturningtype)
+ {
+ case VAR_RETURNING_OLD:
+ scratch.opcode = EEOP_OLD_SYSVAR;
+ break;
+ case VAR_RETURNING_NEW:
+ scratch.opcode = EEOP_NEW_SYSVAR;
+ break;
+ default:
+ scratch.opcode = EEOP_SCAN_SYSVAR;
+ break;
+ }
break;
}
}
@@ -946,11 +983,28 @@ ExecInitExprRec(Expr *node, ExprState *s
/* INDEX_VAR is handled by default case */
default:
- scratch.opcode = EEOP_SCAN_VAR;
+ switch (variable->varreturningtype)
+ {
+ case VAR_RETURNING_OLD:
+ scratch.opcode = EEOP_OLD_VAR;
+ break;
+ case VAR_RETURNING_NEW:
+ scratch.opcode = EEOP_NEW_VAR;
+ break;
+ default:
+ scratch.opcode = EEOP_SCAN_VAR;
+ break;
+ }
break;
}
}
+ /* update the ExprState's flags if Var refers to OLD/NEW */
+ if (variable->varreturningtype == VAR_RETURNING_OLD)
+ state->flags |= EEO_FLAG_HAS_OLD;
+ else if (variable->varreturningtype == VAR_RETURNING_NEW)
+ state->flags |= EEO_FLAG_HAS_NEW;
+
ExprEvalPushStep(state, &scratch);
break;
}
@@ -1407,6 +1461,25 @@ ExecInitExprRec(Expr *node, ExprState *s
sstate = ExecInitSubPlan(subplan, state->parent);
+ /*
+ * If any of the SubPlan's expressions contain uplevel Vars
+ * referring to OLD/NEW, update the ExprState's flags.
+ */
+ if (sstate->testexpr)
+ {
+ if (sstate->testexpr->flags & EEO_FLAG_HAS_OLD)
+ state->flags |= EEO_FLAG_HAS_OLD;
+ if (sstate->testexpr->flags & EEO_FLAG_HAS_NEW)
+ state->flags |= EEO_FLAG_HAS_NEW;
+ }
+ foreach_node(ExprState, argexpr, sstate->args)
+ {
+ if (argexpr->flags & EEO_FLAG_HAS_OLD)
+ state->flags |= EEO_FLAG_HAS_OLD;
+ if (argexpr->flags & EEO_FLAG_HAS_NEW)
+ state->flags |= EEO_FLAG_HAS_NEW;
+ }
+
/* add SubPlanState nodes to state->parent->subPlan */
state->parent->subPlan = lappend(state->parent->subPlan,
sstate);
@@ -2684,7 +2757,7 @@ ExecInitFunc(ExprEvalStep *scratch, Expr
static void
ExecCreateExprSetupSteps(ExprState *state, Node *node)
{
- ExprSetupInfo info = {0, 0, 0, NIL};
+ ExprSetupInfo info = {0, 0, 0, 0, 0, NIL};
/* Prescan to find out what we need. */
expr_setup_walker(node, &info);
@@ -2707,8 +2780,8 @@ ExecPushExprSetupSteps(ExprState *state,
scratch.resnull = NULL;
/*
- * Add steps deforming the ExprState's inner/outer/scan slots as much as
- * required by any Vars appearing in the expression.
+ * Add steps deforming the ExprState's inner/outer/scan/old/new slots as
+ * much as required by any Vars appearing in the expression.
*/
if (info->last_inner > 0)
{
@@ -2740,6 +2813,26 @@ ExecPushExprSetupSteps(ExprState *state,
if (ExecComputeSlotInfo(state, &scratch))
ExprEvalPushStep(state, &scratch);
}
+ if (info->last_old > 0)
+ {
+ scratch.opcode = EEOP_OLD_FETCHSOME;
+ scratch.d.fetch.last_var = info->last_old;
+ scratch.d.fetch.fixed = false;
+ scratch.d.fetch.kind = NULL;
+ scratch.d.fetch.known_desc = NULL;
+ if (ExecComputeSlotInfo(state, &scratch))
+ ExprEvalPushStep(state, &scratch);
+ }
+ if (info->last_new > 0)
+ {
+ scratch.opcode = EEOP_NEW_FETCHSOME;
+ scratch.d.fetch.last_var = info->last_new;
+ scratch.d.fetch.fixed = false;
+ scratch.d.fetch.kind = NULL;
+ scratch.d.fetch.known_desc = NULL;
+ if (ExecComputeSlotInfo(state, &scratch))
+ ExprEvalPushStep(state, &scratch);
+ }
/*
* Add steps to execute any MULTIEXPR SubPlans appearing in the
@@ -2803,7 +2896,18 @@ expr_setup_walker(Node *node, ExprSetupI
/* INDEX_VAR is handled by default case */
default:
- info->last_scan = Max(info->last_scan, attnum);
+ switch (variable->varreturningtype)
+ {
+ case VAR_RETURNING_OLD:
+ info->last_old = Max(info->last_old, attnum);
+ break;
+ case VAR_RETURNING_NEW:
+ info->last_new = Max(info->last_new, attnum);
+ break;
+ default:
+ info->last_scan = Max(info->last_scan, attnum);
+ break;
+ }
break;
}
return false;
@@ -2842,6 +2946,11 @@ expr_setup_walker(Node *node, ExprSetupI
* evaluation of the expression will have the same type of slot, with an
* equivalent descriptor.
*
+ * EEOP_OLD_FETCHSOME and EEOP_NEW_FETCHSOME are used to process RETURNING, if
+ * OLD/NEW columns are referred to explicitly. In both cases, the tuple
+ * descriptor comes from the parent scan node, so we treat them the same as
+ * EEOP_SCAN_FETCHSOME.
+ *
* Returns true if the deforming step is required, false otherwise.
*/
static bool
@@ -2855,7 +2964,9 @@ ExecComputeSlotInfo(ExprState *state, Ex
Assert(opcode == EEOP_INNER_FETCHSOME ||
opcode == EEOP_OUTER_FETCHSOME ||
- opcode == EEOP_SCAN_FETCHSOME);
+ opcode == EEOP_SCAN_FETCHSOME ||
+ opcode == EEOP_OLD_FETCHSOME ||
+ opcode == EEOP_NEW_FETCHSOME);
if (op->d.fetch.known_desc != NULL)
{
@@ -2907,7 +3018,9 @@ ExecComputeSlotInfo(ExprState *state, Ex
desc = ExecGetResultType(os);
}
}
- else if (opcode == EEOP_SCAN_FETCHSOME)
+ else if (opcode == EEOP_SCAN_FETCHSOME ||
+ opcode == EEOP_OLD_FETCHSOME ||
+ opcode == EEOP_NEW_FETCHSOME)
{
desc = parent->scandesc;
@@ -3457,7 +3570,7 @@ ExecBuildAggTrans(AggState *aggstate, Ag
PlanState *parent = &aggstate->ss.ps;
ExprEvalStep scratch = {0};
bool isCombine = DO_AGGSPLIT_COMBINE(aggstate->aggsplit);
- ExprSetupInfo deform = {0, 0, 0, NIL};
+ ExprSetupInfo deform = {0, 0, 0, 0, 0, NIL};
state->expr = (Expr *) aggstate;
state->parent = parent;
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
new file mode 100644
index 7c1f51e..2db47aa
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -157,17 +157,25 @@ static void ExecEvalRowNullInt(ExprState
static Datum ExecJustInnerVar(ExprState *state, ExprContext *econtext, bool *isnull);
static Datum ExecJustOuterVar(ExprState *state, ExprContext *econtext, bool *isnull);
static Datum ExecJustScanVar(ExprState *state, ExprContext *econtext, bool *isnull);
+static Datum ExecJustOldVar(ExprState *state, ExprContext *econtext, bool *isnull);
+static Datum ExecJustNewVar(ExprState *state, ExprContext *econtext, bool *isnull);
static Datum ExecJustAssignInnerVar(ExprState *state, ExprContext *econtext, bool *isnull);
static Datum ExecJustAssignOuterVar(ExprState *state, ExprContext *econtext, bool *isnull);
static Datum ExecJustAssignScanVar(ExprState *state, ExprContext *econtext, bool *isnull);
+static Datum ExecJustAssignOldVar(ExprState *state, ExprContext *econtext, bool *isnull);
+static Datum ExecJustAssignNewVar(ExprState *state, ExprContext *econtext, bool *isnull);
static Datum ExecJustApplyFuncToCase(ExprState *state, ExprContext *econtext, bool *isnull);
static Datum ExecJustConst(ExprState *state, ExprContext *econtext, bool *isnull);
static Datum ExecJustInnerVarVirt(ExprState *state, ExprContext *econtext, bool *isnull);
static Datum ExecJustOuterVarVirt(ExprState *state, ExprContext *econtext, bool *isnull);
static Datum ExecJustScanVarVirt(ExprState *state, ExprContext *econtext, bool *isnull);
+static Datum ExecJustOldVarVirt(ExprState *state, ExprContext *econtext, bool *isnull);
+static Datum ExecJustNewVarVirt(ExprState *state, ExprContext *econtext, bool *isnull);
static Datum ExecJustAssignInnerVarVirt(ExprState *state, ExprContext *econtext, bool *isnull);
static Datum ExecJustAssignOuterVarVirt(ExprState *state, ExprContext *econtext, bool *isnull);
static Datum ExecJustAssignScanVarVirt(ExprState *state, ExprContext *econtext, bool *isnull);
+static Datum ExecJustAssignOldVarVirt(ExprState *state, ExprContext *econtext, bool *isnull);
+static Datum ExecJustAssignNewVarVirt(ExprState *state, ExprContext *econtext, bool *isnull);
/* execution helper functions */
static pg_attribute_always_inline void ExecAggPlainTransByVal(AggState *aggstate,
@@ -295,6 +303,18 @@ ExecReadyInterpretedExpr(ExprState *stat
state->evalfunc_private = (void *) ExecJustScanVar;
return;
}
+ else if (step0 == EEOP_OLD_FETCHSOME &&
+ step1 == EEOP_OLD_VAR)
+ {
+ state->evalfunc_private = (void *) ExecJustOldVar;
+ return;
+ }
+ else if (step0 == EEOP_NEW_FETCHSOME &&
+ step1 == EEOP_NEW_VAR)
+ {
+ state->evalfunc_private = (void *) ExecJustNewVar;
+ return;
+ }
else if (step0 == EEOP_INNER_FETCHSOME &&
step1 == EEOP_ASSIGN_INNER_VAR)
{
@@ -313,6 +333,18 @@ ExecReadyInterpretedExpr(ExprState *stat
state->evalfunc_private = (void *) ExecJustAssignScanVar;
return;
}
+ else if (step0 == EEOP_OLD_FETCHSOME &&
+ step1 == EEOP_ASSIGN_OLD_VAR)
+ {
+ state->evalfunc_private = (void *) ExecJustAssignOldVar;
+ return;
+ }
+ else if (step0 == EEOP_NEW_FETCHSOME &&
+ step1 == EEOP_ASSIGN_NEW_VAR)
+ {
+ state->evalfunc_private = (void *) ExecJustAssignNewVar;
+ return;
+ }
else if (step0 == EEOP_CASE_TESTVAL &&
step1 == EEOP_FUNCEXPR_STRICT &&
state->steps[0].d.casetest.value)
@@ -345,6 +377,16 @@ ExecReadyInterpretedExpr(ExprState *stat
state->evalfunc_private = (void *) ExecJustScanVarVirt;
return;
}
+ else if (step0 == EEOP_OLD_VAR)
+ {
+ state->evalfunc_private = (void *) ExecJustOldVarVirt;
+ return;
+ }
+ else if (step0 == EEOP_NEW_VAR)
+ {
+ state->evalfunc_private = (void *) ExecJustNewVarVirt;
+ return;
+ }
else if (step0 == EEOP_ASSIGN_INNER_VAR)
{
state->evalfunc_private = (void *) ExecJustAssignInnerVarVirt;
@@ -360,6 +402,16 @@ ExecReadyInterpretedExpr(ExprState *stat
state->evalfunc_private = (void *) ExecJustAssignScanVarVirt;
return;
}
+ else if (step0 == EEOP_ASSIGN_OLD_VAR)
+ {
+ state->evalfunc_private = (void *) ExecJustAssignOldVarVirt;
+ return;
+ }
+ else if (step0 == EEOP_ASSIGN_NEW_VAR)
+ {
+ state->evalfunc_private = (void *) ExecJustAssignNewVarVirt;
+ return;
+ }
}
#if defined(EEO_USE_COMPUTED_GOTO)
@@ -399,6 +451,8 @@ ExecInterpExpr(ExprState *state, ExprCon
TupleTableSlot *innerslot;
TupleTableSlot *outerslot;
TupleTableSlot *scanslot;
+ TupleTableSlot *oldslot;
+ TupleTableSlot *newslot;
/*
* This array has to be in the same order as enum ExprEvalOp.
@@ -409,16 +463,24 @@ ExecInterpExpr(ExprState *state, ExprCon
&&CASE_EEOP_INNER_FETCHSOME,
&&CASE_EEOP_OUTER_FETCHSOME,
&&CASE_EEOP_SCAN_FETCHSOME,
+ &&CASE_EEOP_OLD_FETCHSOME,
+ &&CASE_EEOP_NEW_FETCHSOME,
&&CASE_EEOP_INNER_VAR,
&&CASE_EEOP_OUTER_VAR,
&&CASE_EEOP_SCAN_VAR,
+ &&CASE_EEOP_OLD_VAR,
+ &&CASE_EEOP_NEW_VAR,
&&CASE_EEOP_INNER_SYSVAR,
&&CASE_EEOP_OUTER_SYSVAR,
&&CASE_EEOP_SCAN_SYSVAR,
+ &&CASE_EEOP_OLD_SYSVAR,
+ &&CASE_EEOP_NEW_SYSVAR,
&&CASE_EEOP_WHOLEROW,
&&CASE_EEOP_ASSIGN_INNER_VAR,
&&CASE_EEOP_ASSIGN_OUTER_VAR,
&&CASE_EEOP_ASSIGN_SCAN_VAR,
+ &&CASE_EEOP_ASSIGN_OLD_VAR,
+ &&CASE_EEOP_ASSIGN_NEW_VAR,
&&CASE_EEOP_ASSIGN_TMP,
&&CASE_EEOP_ASSIGN_TMP_MAKE_RO,
&&CASE_EEOP_CONST,
@@ -518,6 +580,8 @@ ExecInterpExpr(ExprState *state, ExprCon
innerslot = econtext->ecxt_innertuple;
outerslot = econtext->ecxt_outertuple;
scanslot = econtext->ecxt_scantuple;
+ oldslot = econtext->ecxt_oldtuple;
+ newslot = econtext->ecxt_newtuple;
#if defined(EEO_USE_COMPUTED_GOTO)
EEO_DISPATCH();
@@ -557,6 +621,24 @@ ExecInterpExpr(ExprState *state, ExprCon
EEO_NEXT();
}
+ EEO_CASE(EEOP_OLD_FETCHSOME)
+ {
+ CheckOpSlotCompatibility(op, oldslot);
+
+ slot_getsomeattrs(oldslot, op->d.fetch.last_var);
+
+ EEO_NEXT();
+ }
+
+ EEO_CASE(EEOP_NEW_FETCHSOME)
+ {
+ CheckOpSlotCompatibility(op, newslot);
+
+ slot_getsomeattrs(newslot, op->d.fetch.last_var);
+
+ EEO_NEXT();
+ }
+
EEO_CASE(EEOP_INNER_VAR)
{
int attnum = op->d.var.attnum;
@@ -600,6 +682,32 @@ ExecInterpExpr(ExprState *state, ExprCon
EEO_NEXT();
}
+ EEO_CASE(EEOP_OLD_VAR)
+ {
+ int attnum = op->d.var.attnum;
+
+ /* See EEOP_INNER_VAR comments */
+
+ Assert(attnum >= 0 && attnum < oldslot->tts_nvalid);
+ *op->resvalue = oldslot->tts_values[attnum];
+ *op->resnull = oldslot->tts_isnull[attnum];
+
+ EEO_NEXT();
+ }
+
+ EEO_CASE(EEOP_NEW_VAR)
+ {
+ int attnum = op->d.var.attnum;
+
+ /* See EEOP_INNER_VAR comments */
+
+ Assert(attnum >= 0 && attnum < newslot->tts_nvalid);
+ *op->resvalue = newslot->tts_values[attnum];
+ *op->resnull = newslot->tts_isnull[attnum];
+
+ EEO_NEXT();
+ }
+
EEO_CASE(EEOP_INNER_SYSVAR)
{
ExecEvalSysVar(state, op, econtext, innerslot);
@@ -618,6 +726,18 @@ ExecInterpExpr(ExprState *state, ExprCon
EEO_NEXT();
}
+ EEO_CASE(EEOP_OLD_SYSVAR)
+ {
+ ExecEvalSysVar(state, op, econtext, oldslot);
+ EEO_NEXT();
+ }
+
+ EEO_CASE(EEOP_NEW_SYSVAR)
+ {
+ ExecEvalSysVar(state, op, econtext, newslot);
+ EEO_NEXT();
+ }
+
EEO_CASE(EEOP_WHOLEROW)
{
/* too complex for an inline implementation */
@@ -677,6 +797,40 @@ ExecInterpExpr(ExprState *state, ExprCon
EEO_NEXT();
}
+ EEO_CASE(EEOP_ASSIGN_OLD_VAR)
+ {
+ int resultnum = op->d.assign_var.resultnum;
+ int attnum = op->d.assign_var.attnum;
+
+ /*
+ * We do not need CheckVarSlotCompatibility here; that was taken
+ * care of at compilation time. But see EEOP_INNER_VAR comments.
+ */
+ Assert(attnum >= 0 && attnum < oldslot->tts_nvalid);
+ Assert(resultnum >= 0 && resultnum < resultslot->tts_tupleDescriptor->natts);
+ resultslot->tts_values[resultnum] = oldslot->tts_values[attnum];
+ resultslot->tts_isnull[resultnum] = oldslot->tts_isnull[attnum];
+
+ EEO_NEXT();
+ }
+
+ EEO_CASE(EEOP_ASSIGN_NEW_VAR)
+ {
+ int resultnum = op->d.assign_var.resultnum;
+ int attnum = op->d.assign_var.attnum;
+
+ /*
+ * We do not need CheckVarSlotCompatibility here; that was taken
+ * care of at compilation time. But see EEOP_INNER_VAR comments.
+ */
+ Assert(attnum >= 0 && attnum < newslot->tts_nvalid);
+ Assert(resultnum >= 0 && resultnum < resultslot->tts_tupleDescriptor->natts);
+ resultslot->tts_values[resultnum] = newslot->tts_values[attnum];
+ resultslot->tts_isnull[resultnum] = newslot->tts_isnull[attnum];
+
+ EEO_NEXT();
+ }
+
EEO_CASE(EEOP_ASSIGN_TMP)
{
int resultnum = op->d.assign_tmp.resultnum;
@@ -1890,10 +2044,14 @@ CheckExprStillValid(ExprState *state, Ex
TupleTableSlot *innerslot;
TupleTableSlot *outerslot;
TupleTableSlot *scanslot;
+ TupleTableSlot *oldslot;
+ TupleTableSlot *newslot;
innerslot = econtext->ecxt_innertuple;
outerslot = econtext->ecxt_outertuple;
scanslot = econtext->ecxt_scantuple;
+ oldslot = econtext->ecxt_oldtuple;
+ newslot = econtext->ecxt_newtuple;
for (int i = 0; i < state->steps_len; i++)
{
@@ -1924,6 +2082,22 @@ CheckExprStillValid(ExprState *state, Ex
CheckVarSlotCompatibility(scanslot, attnum + 1, op->d.var.vartype);
break;
}
+
+ case EEOP_OLD_VAR:
+ {
+ int attnum = op->d.var.attnum;
+
+ CheckVarSlotCompatibility(oldslot, attnum + 1, op->d.var.vartype);
+ break;
+ }
+
+ case EEOP_NEW_VAR:
+ {
+ int attnum = op->d.var.attnum;
+
+ CheckVarSlotCompatibility(newslot, attnum + 1, op->d.var.vartype);
+ break;
+ }
default:
break;
}
@@ -2098,7 +2272,7 @@ get_cached_rowtype(Oid type_id, int32 ty
* Fast-path functions, for very simple expressions
*/
-/* implementation of ExecJust(Inner|Outer|Scan)Var */
+/* implementation of ExecJust(Inner|Outer|Scan|Old|New)Var */
static pg_attribute_always_inline Datum
ExecJustVarImpl(ExprState *state, TupleTableSlot *slot, bool *isnull)
{
@@ -2136,7 +2310,21 @@ ExecJustScanVar(ExprState *state, ExprCo
return ExecJustVarImpl(state, econtext->ecxt_scantuple, isnull);
}
-/* implementation of ExecJustAssign(Inner|Outer|Scan)Var */
+/* Simple reference to OLD Var in RETURNING */
+static Datum
+ExecJustOldVar(ExprState *state, ExprContext *econtext, bool *isnull)
+{
+ return ExecJustVarImpl(state, econtext->ecxt_oldtuple, isnull);
+}
+
+/* Simple reference to NEW Var in RETURNING */
+static Datum
+ExecJustNewVar(ExprState *state, ExprContext *econtext, bool *isnull)
+{
+ return ExecJustVarImpl(state, econtext->ecxt_newtuple, isnull);
+}
+
+/* implementation of ExecJustAssign(Inner|Outer|Scan|Old|New)Var */
static pg_attribute_always_inline Datum
ExecJustAssignVarImpl(ExprState *state, TupleTableSlot *inslot, bool *isnull)
{
@@ -2183,6 +2371,20 @@ ExecJustAssignScanVar(ExprState *state,
return ExecJustAssignVarImpl(state, econtext->ecxt_scantuple, isnull);
}
+/* Evaluate OLD Var and assign to appropriate column of result tuple */
+static Datum
+ExecJustAssignOldVar(ExprState *state, ExprContext *econtext, bool *isnull)
+{
+ return ExecJustAssignVarImpl(state, econtext->ecxt_oldtuple, isnull);
+}
+
+/* Evaluate NEW Var and assign to appropriate column of result tuple */
+static Datum
+ExecJustAssignNewVar(ExprState *state, ExprContext *econtext, bool *isnull)
+{
+ return ExecJustAssignVarImpl(state, econtext->ecxt_newtuple, isnull);
+}
+
/* Evaluate CASE_TESTVAL and apply a strict function to it */
static Datum
ExecJustApplyFuncToCase(ExprState *state, ExprContext *econtext, bool *isnull)
@@ -2231,7 +2433,7 @@ ExecJustConst(ExprState *state, ExprCont
return op->d.constval.value;
}
-/* implementation of ExecJust(Inner|Outer|Scan)VarVirt */
+/* implementation of ExecJust(Inner|Outer|Scan|Old|New)VarVirt */
static pg_attribute_always_inline Datum
ExecJustVarVirtImpl(ExprState *state, TupleTableSlot *slot, bool *isnull)
{
@@ -2274,7 +2476,21 @@ ExecJustScanVarVirt(ExprState *state, Ex
return ExecJustVarVirtImpl(state, econtext->ecxt_scantuple, isnull);
}
-/* implementation of ExecJustAssign(Inner|Outer|Scan)VarVirt */
+/* Like ExecJustOldVar, optimized for virtual slots */
+static Datum
+ExecJustOldVarVirt(ExprState *state, ExprContext *econtext, bool *isnull)
+{
+ return ExecJustVarVirtImpl(state, econtext->ecxt_oldtuple, isnull);
+}
+
+/* Like ExecJustNewVar, optimized for virtual slots */
+static Datum
+ExecJustNewVarVirt(ExprState *state, ExprContext *econtext, bool *isnull)
+{
+ return ExecJustVarVirtImpl(state, econtext->ecxt_newtuple, isnull);
+}
+
+/* implementation of ExecJustAssign(Inner|Outer|Scan|Old|New)VarVirt */
static pg_attribute_always_inline Datum
ExecJustAssignVarVirtImpl(ExprState *state, TupleTableSlot *inslot, bool *isnull)
{
@@ -2317,6 +2533,20 @@ ExecJustAssignScanVarVirt(ExprState *sta
return ExecJustAssignVarVirtImpl(state, econtext->ecxt_scantuple, isnull);
}
+/* Like ExecJustAssignOldVar, optimized for virtual slots */
+static Datum
+ExecJustAssignOldVarVirt(ExprState *state, ExprContext *econtext, bool *isnull)
+{
+ return ExecJustAssignVarVirtImpl(state, econtext->ecxt_oldtuple, isnull);
+}
+
+/* Like ExecJustAssignNewVar, optimized for virtual slots */
+static Datum
+ExecJustAssignNewVarVirt(ExprState *state, ExprContext *econtext, bool *isnull)
+{
+ return ExecJustAssignVarVirtImpl(state, econtext->ecxt_newtuple, isnull);
+}
+
#if defined(EEO_USE_COMPUTED_GOTO)
/*
* Comparator used when building address->opcode lookup table for
@@ -4294,8 +4524,25 @@ ExecEvalWholeRowVar(ExprState *state, Ex
/* INDEX_VAR is handled by default case */
default:
- /* get the tuple from the relation being scanned */
- slot = econtext->ecxt_scantuple;
+
+ /*
+ * Get the tuple from the relation being scanned.
+ *
+ * By default, this uses the "scan" tuple slot, but a wholerow Var
+ * in the RETURNING list may explicitly refer to OLD/NEW.
+ */
+ switch (variable->varreturningtype)
+ {
+ case VAR_RETURNING_OLD:
+ slot = econtext->ecxt_oldtuple;
+ break;
+ case VAR_RETURNING_NEW:
+ slot = econtext->ecxt_newtuple;
+ break;
+ default:
+ slot = econtext->ecxt_scantuple;
+ break;
+ }
break;
}
@@ -4503,9 +4750,6 @@ ExecEvalSysVar(ExprState *state, ExprEva
op->d.var.attnum,
op->resnull);
*op->resvalue = d;
- /* this ought to be unreachable, but it's cheap enough to check */
- if (unlikely(*op->resnull))
- elog(ERROR, "failed to fetch attribute from slot");
}
/*
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 9351fbc..02f66a6
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -95,6 +95,13 @@ typedef struct ModifyTableContext
TM_FailureData tmfd;
/*
+ * The tuple deleted when doing a cross-partition UPDATE with a RETURNING
+ * clause that refers to OLD columns (converted to the root's tuple
+ * descriptor).
+ */
+ TupleTableSlot *cpDeletedSlot;
+
+ /*
* The tuple projected by the INSERT's RETURNING clause, when doing a
* cross-partition UPDATE
*/
@@ -236,34 +243,41 @@ ExecCheckPlanOutput(Relation resultRel,
* ExecProcessReturning --- evaluate a RETURNING list
*
* resultRelInfo: current result rel
- * tupleSlot: slot holding tuple actually inserted/updated/deleted
+ * cmdType: operation performed (INSERT, UPDATE, or DELETE only)
+ * oldSlot: slot holding old tuple deleted or updated
+ * newSlot: slot holding new tuple inserted or updated
* planSlot: slot holding tuple returned by top subplan node
*
- * Note: If tupleSlot is NULL, the FDW should have already provided econtext's
- * scan tuple.
+ * Note: If oldSlot/newSlot are NULL, the FDW should have already provided
+ * econtext's scan/old/new tuples.
*
* Returns a slot holding the result tuple
*/
static TupleTableSlot *
ExecProcessReturning(ResultRelInfo *resultRelInfo,
- TupleTableSlot *tupleSlot,
+ CmdType cmdType,
+ TupleTableSlot *oldSlot,
+ TupleTableSlot *newSlot,
TupleTableSlot *planSlot)
{
ProjectionInfo *projectReturning = resultRelInfo->ri_projectReturning;
ExprContext *econtext = projectReturning->pi_exprContext;
- /* Make tuple and any needed join variables available to ExecProject */
- if (tupleSlot)
- econtext->ecxt_scantuple = tupleSlot;
+ /* Make tuples and any needed join variables available to ExecProject */
+ if (oldSlot)
+ {
+ econtext->ecxt_oldtuple = oldSlot;
+ if (cmdType == CMD_DELETE)
+ econtext->ecxt_scantuple = oldSlot;
+ }
+ if (newSlot)
+ {
+ econtext->ecxt_newtuple = newSlot;
+ if (cmdType != CMD_DELETE)
+ econtext->ecxt_scantuple = newSlot;
+ }
econtext->ecxt_outertuple = planSlot;
- /*
- * RETURNING expressions might reference the tableoid column, so
- * reinitialize tts_tableOid before evaluating them.
- */
- econtext->ecxt_scantuple->tts_tableOid =
- RelationGetRelid(resultRelInfo->ri_RelationDesc);
-
/* Compute the RETURNING expressions */
return ExecProject(projectReturning);
}
@@ -759,6 +773,7 @@ ExecInsert(ModifyTableContext *context,
Relation resultRelationDesc;
List *recheckIndexes = NIL;
TupleTableSlot *planSlot = context->planSlot;
+ TupleTableSlot *oldSlot;
TupleTableSlot *result = NULL;
TransitionCaptureState *ar_insert_trig_tcs;
ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
@@ -1193,7 +1208,63 @@ ExecInsert(ModifyTableContext *context,
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
- result = ExecProcessReturning(resultRelInfo, slot, planSlot);
+ {
+ /*
+ * If this is part of a cross-partition UPDATE, and the RETURNING list
+ * refers to any OLD columns, ExecDelete() will have saved the tuple
+ * deleted from the original partition, which we must use here to
+ * compute the OLD column values. Otherwise, set all OLD column
+ * values to NULL, if requested.
+ */
+ if (context->cpDeletedSlot)
+ {
+ TupleConversionMap *tupconv_map;
+
+ /*
+ * Convert the OLD tuple to the new partition's format/slot, if
+ * needed. Note that ExceDelete() already converted it to the
+ * root's partition's format/slot.
+ */
+ oldSlot = context->cpDeletedSlot;
+ tupconv_map = ExecGetRootToChildMap(resultRelInfo, estate);
+ if (tupconv_map != NULL)
+ {
+ oldSlot = execute_attr_map_slot(tupconv_map->attrMap,
+ oldSlot,
+ ExecGetReturningSlot(estate,
+ resultRelInfo));
+
+ oldSlot->tts_tableOid = context->cpDeletedSlot->tts_tableOid;
+ ItemPointerCopy(&context->cpDeletedSlot->tts_tid, &oldSlot->tts_tid);
+ }
+ }
+ else if (resultRelInfo->ri_projectReturning->pi_state.flags & EEO_FLAG_HAS_OLD)
+ {
+ oldSlot = ExecGetReturningSlot(estate, resultRelInfo);
+
+ ExecStoreAllNullTuple(oldSlot);
+ oldSlot->tts_tableOid = RelationGetRelid(resultRelInfo->ri_RelationDesc);
+ }
+ else
+ oldSlot = NULL; /* No references to OLD columns */
+
+ result = ExecProcessReturning(resultRelInfo, CMD_INSERT,
+ oldSlot, slot, planSlot);
+
+ /*
+ * For a cross-partition UPDATE, release the old tuple, first making
+ * sure that the result slot has a local copy of any pass-by-reference
+ * values.
+ */
+ if (context->cpDeletedSlot)
+ {
+ ExecMaterializeSlot(result);
+ ExecClearTuple(oldSlot);
+ if (context->cpDeletedSlot != oldSlot)
+ ExecClearTuple(context->cpDeletedSlot);
+ context->cpDeletedSlot = NULL;
+ }
+ }
if (inserted_tuple)
*inserted_tuple = slot;
@@ -1431,6 +1502,7 @@ ExecDelete(ModifyTableContext *context,
Relation resultRelationDesc = resultRelInfo->ri_RelationDesc;
TupleTableSlot *slot = NULL;
TM_Result result;
+ bool saveOld;
if (tupleDeleted)
*tupleDeleted = false;
@@ -1665,13 +1737,23 @@ ldelete:
ExecDeleteEpilogue(context, resultRelInfo, tupleid, oldtuple, changingPart);
- /* Process RETURNING if present and if requested */
- if (processReturning && resultRelInfo->ri_projectReturning)
+ /*
+ * Process RETURNING if present and if requested.
+ *
+ * If this is part of a cross-partition UPDATE, and the RETURNING list
+ * refers to any OLD column values, save the old tuple here for later
+ * processing of the RETURNING list by ExecInsert().
+ */
+ saveOld = changingPart && resultRelInfo->ri_projectReturning &&
+ resultRelInfo->ri_projectReturning->pi_state.flags & EEO_FLAG_HAS_OLD;
+
+ if (resultRelInfo->ri_projectReturning && (processReturning || saveOld))
{
/*
* We have to put the target tuple into a slot, which means first we
* gotta fetch it. We can use the trigger tuple slot.
*/
+ TupleTableSlot *newSlot;
TupleTableSlot *rslot;
if (resultRelInfo->ri_FdwRoutine)
@@ -1694,7 +1776,57 @@ ldelete:
}
}
- rslot = ExecProcessReturning(resultRelInfo, slot, context->planSlot);
+ /*
+ * If required, save the old tuple for later processing of the
+ * RETURNING list by ExecInsert().
+ */
+ if (saveOld)
+ {
+ TupleConversionMap *tupconv_map;
+ ResultRelInfo *rootRelInfo;
+ TupleTableSlot *oldSlot;
+
+ /*
+ * Convert the tuple into the root partition's format/slot, if
+ * needed. ExecInsert() will then convert it to the new
+ * partition's format/slot, if necessary.
+ */
+ tupconv_map = ExecGetChildToRootMap(resultRelInfo);
+ if (tupconv_map != NULL)
+ {
+ rootRelInfo = context->mtstate->rootResultRelInfo;
+ oldSlot = slot;
+ slot = execute_attr_map_slot(tupconv_map->attrMap,
+ slot,
+ ExecGetReturningSlot(estate,
+ rootRelInfo));
+
+ slot->tts_tableOid = oldSlot->tts_tableOid;
+ ItemPointerCopy(&oldSlot->tts_tid, &slot->tts_tid);
+ }
+
+ context->cpDeletedSlot = slot;
+
+ return NULL;
+ }
+
+ /*
+ * If the RETURNING list refers to NEW columns, return NULLs. Use
+ * ExecGetTriggerNewSlot() to store an all-NULL new tuple, since it is
+ * of the right type, and isn't being used for anything else.
+ */
+ if (resultRelInfo->ri_projectReturning->pi_state.flags & EEO_FLAG_HAS_NEW)
+ {
+ newSlot = ExecGetTriggerNewSlot(estate, resultRelInfo);
+
+ ExecStoreAllNullTuple(newSlot);
+ newSlot->tts_tableOid = RelationGetRelid(resultRelInfo->ri_RelationDesc);
+ }
+ else
+ newSlot = NULL; /* No references to NEW columns */
+
+ rslot = ExecProcessReturning(resultRelInfo, CMD_DELETE,
+ slot, newSlot, context->planSlot);
/*
* Before releasing the target tuple again, make sure rslot has a
@@ -1747,6 +1879,7 @@ ExecCrossPartitionUpdate(ModifyTableCont
bool tuple_deleted;
TupleTableSlot *epqslot = NULL;
+ context->cpDeletedSlot = NULL;
context->cpUpdateReturningSlot = NULL;
*retry_slot = NULL;
@@ -2248,6 +2381,7 @@ ExecCrossPartitionUpdateForeignKey(Modif
* foreign table triggers; it is NULL when the foreign table has
* no relevant triggers.
*
+ * oldSlot contains the old tuple value.
* slot contains the new tuple value to be stored.
* planSlot is the output of the ModifyTable's subplan; we use it
* to access values from other input tables (for RETURNING),
@@ -2258,8 +2392,8 @@ ExecCrossPartitionUpdateForeignKey(Modif
*/
static TupleTableSlot *
ExecUpdate(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
- ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *slot,
- bool canSetTag)
+ ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *oldSlot,
+ TupleTableSlot *slot, bool canSetTag)
{
EState *estate = context->estate;
Relation resultRelationDesc = resultRelInfo->ri_RelationDesc;
@@ -2374,7 +2508,6 @@ redo_act:
{
TupleTableSlot *inputslot;
TupleTableSlot *epqslot;
- TupleTableSlot *oldSlot;
if (IsolationUsesXactSnapshot())
ereport(ERROR,
@@ -2481,7 +2614,8 @@ redo_act:
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
- return ExecProcessReturning(resultRelInfo, slot, context->planSlot);
+ return ExecProcessReturning(resultRelInfo, CMD_UPDATE,
+ oldSlot, slot, context->planSlot);
return NULL;
}
@@ -2693,16 +2827,23 @@ ExecOnConflictUpdate(ModifyTableContext
/* Execute UPDATE with projection */
*returning = ExecUpdate(context, resultRelInfo,
- conflictTid, NULL,
+ conflictTid, NULL, existing,
resultRelInfo->ri_onConflict->oc_ProjSlot,
canSetTag);
/*
* Clear out existing tuple, as there might not be another conflict among
* the next input rows. Don't want to hold resources till the end of the
- * query.
+ * query. First though, make sure that the returning slot, if any, has a
+ * local copy of any OLD pass-by-reference values, if it refers to any OLD
+ * columns.
*/
+ if (*returning != NULL &&
+ resultRelInfo->ri_projectReturning->pi_state.flags & EEO_FLAG_HAS_OLD)
+ ExecMaterializeSlot(*returning);
+
ExecClearTuple(existing);
+
return true;
}
@@ -3700,6 +3841,7 @@ ExecModifyTable(PlanState *pstate)
ResetExprContext(pstate->ps_ExprContext);
context.planSlot = ExecProcNode(subplanstate);
+ context.cpDeletedSlot = NULL;
/* No more tuples to process? */
if (TupIsNull(context.planSlot))
@@ -3759,9 +3901,12 @@ ExecModifyTable(PlanState *pstate)
* A scan slot containing the data that was actually inserted,
* updated or deleted has already been made available to
* ExecProcessReturning by IterateDirectModify, so no need to
- * provide it here.
+ * provide it here. The individual old and new slots are not
+ * needed, since RETURNING OLD/NEW is not supported for foreign
+ * tables.
*/
- slot = ExecProcessReturning(resultRelInfo, NULL, context.planSlot);
+ slot = ExecProcessReturning(resultRelInfo, operation,
+ NULL, NULL, context.planSlot);
return slot;
}
@@ -3928,7 +4073,7 @@ ExecModifyTable(PlanState *pstate)
/* Now apply the update. */
slot = ExecUpdate(&context, resultRelInfo, tupleid, oldtuple,
- slot, node->canSetTag);
+ oldSlot, slot, node->canSetTag);
break;
case CMD_DELETE:
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
new file mode 100644
index 0c44842..870c27d
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -106,6 +106,8 @@ llvm_compile_expr(ExprState *state)
LLVMValueRef v_outerslot;
LLVMValueRef v_scanslot;
LLVMValueRef v_resultslot;
+ LLVMValueRef v_oldslot;
+ LLVMValueRef v_newslot;
/* nulls/values of slots */
LLVMValueRef v_innervalues;
@@ -114,6 +116,10 @@ llvm_compile_expr(ExprState *state)
LLVMValueRef v_outernulls;
LLVMValueRef v_scanvalues;
LLVMValueRef v_scannulls;
+ LLVMValueRef v_oldvalues;
+ LLVMValueRef v_oldnulls;
+ LLVMValueRef v_newvalues;
+ LLVMValueRef v_newnulls;
LLVMValueRef v_resultvalues;
LLVMValueRef v_resultnulls;
@@ -205,6 +211,16 @@ llvm_compile_expr(ExprState *state)
v_state,
FIELDNO_EXPRSTATE_RESULTSLOT,
"v_resultslot");
+ v_oldslot = l_load_struct_gep(b,
+ StructExprContext,
+ v_econtext,
+ FIELDNO_EXPRCONTEXT_OLDTUPLE,
+ "v_oldslot");
+ v_newslot = l_load_struct_gep(b,
+ StructExprContext,
+ v_econtext,
+ FIELDNO_EXPRCONTEXT_NEWTUPLE,
+ "v_newslot");
/* build global values/isnull pointers */
v_scanvalues = l_load_struct_gep(b,
@@ -217,6 +233,26 @@ llvm_compile_expr(ExprState *state)
v_scanslot,
FIELDNO_TUPLETABLESLOT_ISNULL,
"v_scannulls");
+ v_oldvalues = l_load_struct_gep(b,
+ StructTupleTableSlot,
+ v_oldslot,
+ FIELDNO_TUPLETABLESLOT_VALUES,
+ "v_oldvalues");
+ v_oldnulls = l_load_struct_gep(b,
+ StructTupleTableSlot,
+ v_oldslot,
+ FIELDNO_TUPLETABLESLOT_ISNULL,
+ "v_oldnulls");
+ v_newvalues = l_load_struct_gep(b,
+ StructTupleTableSlot,
+ v_newslot,
+ FIELDNO_TUPLETABLESLOT_VALUES,
+ "v_newvalues");
+ v_newnulls = l_load_struct_gep(b,
+ StructTupleTableSlot,
+ v_newslot,
+ FIELDNO_TUPLETABLESLOT_ISNULL,
+ "v_newnulls");
v_innervalues = l_load_struct_gep(b,
StructTupleTableSlot,
v_innerslot,
@@ -302,6 +338,8 @@ llvm_compile_expr(ExprState *state)
case EEOP_INNER_FETCHSOME:
case EEOP_OUTER_FETCHSOME:
case EEOP_SCAN_FETCHSOME:
+ case EEOP_OLD_FETCHSOME:
+ case EEOP_NEW_FETCHSOME:
{
TupleDesc desc = NULL;
LLVMValueRef v_slot;
@@ -326,8 +364,12 @@ llvm_compile_expr(ExprState *state)
v_slot = v_innerslot;
else if (opcode == EEOP_OUTER_FETCHSOME)
v_slot = v_outerslot;
- else
+ else if (opcode == EEOP_SCAN_FETCHSOME)
v_slot = v_scanslot;
+ else if (opcode == EEOP_OLD_FETCHSOME)
+ v_slot = v_oldslot;
+ else
+ v_slot = v_newslot;
/*
* Check if all required attributes are available, or
@@ -396,6 +438,8 @@ llvm_compile_expr(ExprState *state)
case EEOP_INNER_VAR:
case EEOP_OUTER_VAR:
case EEOP_SCAN_VAR:
+ case EEOP_OLD_VAR:
+ case EEOP_NEW_VAR:
{
LLVMValueRef value,
isnull;
@@ -413,11 +457,21 @@ llvm_compile_expr(ExprState *state)
v_values = v_outervalues;
v_nulls = v_outernulls;
}
- else
+ else if (opcode == EEOP_SCAN_VAR)
{
v_values = v_scanvalues;
v_nulls = v_scannulls;
}
+ else if (opcode == EEOP_OLD_VAR)
+ {
+ v_values = v_oldvalues;
+ v_nulls = v_oldnulls;
+ }
+ else
+ {
+ v_values = v_newvalues;
+ v_nulls = v_newnulls;
+ }
v_attnum = l_int32_const(lc, op->d.var.attnum);
value = l_load_gep1(b, TypeSizeT, v_values, v_attnum, "");
@@ -432,6 +486,8 @@ llvm_compile_expr(ExprState *state)
case EEOP_INNER_SYSVAR:
case EEOP_OUTER_SYSVAR:
case EEOP_SCAN_SYSVAR:
+ case EEOP_OLD_SYSVAR:
+ case EEOP_NEW_SYSVAR:
{
LLVMValueRef v_slot;
@@ -439,8 +495,12 @@ llvm_compile_expr(ExprState *state)
v_slot = v_innerslot;
else if (opcode == EEOP_OUTER_SYSVAR)
v_slot = v_outerslot;
- else
+ else if (opcode == EEOP_SCAN_SYSVAR)
v_slot = v_scanslot;
+ else if (opcode == EEOP_OLD_SYSVAR)
+ v_slot = v_oldslot;
+ else
+ v_slot = v_newslot;
build_EvalXFunc(b, mod, "ExecEvalSysVar",
v_state, op, v_econtext, v_slot);
@@ -458,6 +518,8 @@ llvm_compile_expr(ExprState *state)
case EEOP_ASSIGN_INNER_VAR:
case EEOP_ASSIGN_OUTER_VAR:
case EEOP_ASSIGN_SCAN_VAR:
+ case EEOP_ASSIGN_OLD_VAR:
+ case EEOP_ASSIGN_NEW_VAR:
{
LLVMValueRef v_value;
LLVMValueRef v_isnull;
@@ -478,11 +540,21 @@ llvm_compile_expr(ExprState *state)
v_values = v_outervalues;
v_nulls = v_outernulls;
}
- else
+ else if (opcode == EEOP_ASSIGN_SCAN_VAR)
{
v_values = v_scanvalues;
v_nulls = v_scannulls;
}
+ else if (opcode == EEOP_ASSIGN_OLD_VAR)
+ {
+ v_values = v_oldvalues;
+ v_nulls = v_oldnulls;
+ }
+ else
+ {
+ v_values = v_newvalues;
+ v_nulls = v_newnulls;
+ }
/* load data */
v_attnum = l_int32_const(lc, op->d.assign_var.attnum);
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
new file mode 100644
index 33d4d23..a2945ad
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -80,12 +80,14 @@ makeVar(int varno,
var->varlevelsup = varlevelsup;
/*
- * Only a few callers need to make Var nodes with non-null varnullingrels,
- * or with varnosyn/varattnosyn different from varno/varattno. We don't
- * provide separate arguments for them, but just initialize them to NULL
- * and the given varno/varattno. This reduces code clutter and chance of
- * error for most callers.
+ * Only a few callers need to make Var nodes with varreturningtype
+ * different from VAR_RETURNING_DEFAULT, non-null varnullingrels, or with
+ * varnosyn/varattnosyn different from varno/varattno. We don't provide
+ * separate arguments for them, but just initialize them to sensible
+ * default values. This reduces code clutter and chance of error for most
+ * callers.
*/
+ var->varreturningtype = VAR_RETURNING_DEFAULT;
var->varnullingrels = NULL;
var->varnosyn = (Index) varno;
var->varattnosyn = varattno;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
new file mode 100644
index 6ba8e73..c01b4e0
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -3832,6 +3832,7 @@ raw_expression_tree_walker_impl(Node *no
case T_ParamRef:
case T_A_Const:
case T_A_Star:
+ case T_ReturningOption:
/* primitive node types with no subnodes */
break;
case T_Alias:
@@ -4000,7 +4001,7 @@ raw_expression_tree_walker_impl(Node *no
return true;
if (WALK(stmt->onConflictClause))
return true;
- if (WALK(stmt->returningList))
+ if (WALK(stmt->returningClause))
return true;
if (WALK(stmt->withClause))
return true;
@@ -4016,7 +4017,7 @@ raw_expression_tree_walker_impl(Node *no
return true;
if (WALK(stmt->whereClause))
return true;
- if (WALK(stmt->returningList))
+ if (WALK(stmt->returningClause))
return true;
if (WALK(stmt->withClause))
return true;
@@ -4034,7 +4035,7 @@ raw_expression_tree_walker_impl(Node *no
return true;
if (WALK(stmt->fromClause))
return true;
- if (WALK(stmt->returningList))
+ if (WALK(stmt->returningClause))
return true;
if (WALK(stmt->withClause))
return true;
@@ -4068,6 +4069,16 @@ raw_expression_tree_walker_impl(Node *no
return true;
}
break;
+ case T_ReturningClause:
+ {
+ ReturningClause *returning = (ReturningClause *) node;
+
+ if (WALK(returning->options))
+ return true;
+ if (WALK(returning->exprs))
+ return true;
+ }
+ break;
case T_SelectStmt:
{
SelectStmt *stmt = (SelectStmt *) node;
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
new file mode 100644
index 610f4a5..7bc3477
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -7081,6 +7081,8 @@ make_modifytable(PlannerInfo *root, Plan
}
node->updateColnosLists = updateColnosLists;
node->withCheckOptionLists = withCheckOptionLists;
+ node->returningOld = root->parse->returningOld;
+ node->returningNew = root->parse->returningNew;
node->returningLists = returningLists;
node->rowMarks = rowMarks;
node->mergeActionLists = mergeActionLists;
@@ -7148,7 +7150,8 @@ make_modifytable(PlannerInfo *root, Plan
* callback functions needed for that and (2) there are no local
* structures that need to be run for each modified row: row-level
* triggers on the foreign table, stored generated columns, WITH CHECK
- * OPTIONs from parent views.
+ * OPTIONs from parent views, or Vars returning OLD/NEW in the
+ * RETURNING list.
*/
direct_modify = false;
if (fdwroutine != NULL &&
@@ -7158,7 +7161,8 @@ make_modifytable(PlannerInfo *root, Plan
fdwroutine->EndDirectModify != NULL &&
withCheckOptionLists == NIL &&
!has_row_triggers(root, rti, operation) &&
- !has_stored_generated_columns(root, rti))
+ !has_stored_generated_columns(root, rti) &&
+ !contain_vars_returning_old_or_new((Node *) root->parse->returningList))
direct_modify = fdwroutine->PlanDirectModify(root, node, rti, i);
if (direct_modify)
direct_modify_plans = bms_add_member(direct_modify_plans, i);
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
new file mode 100644
index 300691c..936d519
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -2381,7 +2381,8 @@ pullup_replace_vars_callback(Var *var,
* expansion with varlevelsup = 0, and then adjust below if needed.
*/
expandRTE(rcon->target_rte,
- var->varno, 0 /* not varlevelsup */ , var->location,
+ var->varno, 0 /* not varlevelsup */ ,
+ var->varreturningtype, var->location,
(var->vartype != RECORDOID),
&colnames, &fields);
/* Expand the generated per-field Vars, but don't insert PHVs there */
diff --git a/src/backend/optimizer/util/appendinfo.c b/src/backend/optimizer/util/appendinfo.c
new file mode 100644
index 6ba4eba..33348f5
--- a/src/backend/optimizer/util/appendinfo.c
+++ b/src/backend/optimizer/util/appendinfo.c
@@ -279,7 +279,10 @@ adjust_appendrel_attrs_mutator(Node *nod
elog(ERROR, "attribute %d of relation \"%s\" does not exist",
var->varattno, get_rel_name(appinfo->parent_reloid));
if (IsA(newnode, Var))
+ {
+ ((Var *) newnode)->varreturningtype = var->varreturningtype;
((Var *) newnode)->varnullingrels = var->varnullingrels;
+ }
else if (var->varnullingrels != NULL)
elog(ERROR, "failed to apply nullingrels to a non-Var");
return newnode;
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
new file mode 100644
index 62de022..f20f016
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -3372,6 +3372,8 @@ eval_const_expressions_mutator(Node *nod
fselect->resulttypmod,
fselect->resultcollid,
((Var *) arg)->varlevelsup);
+ /* New Var has same OLD/NEW returning as old one */
+ newvar->varreturningtype = ((Var *) arg)->varreturningtype;
/* New Var is nullable by same rels as the old one */
newvar->varnullingrels = ((Var *) arg)->varnullingrels;
return (Node *) newvar;
diff --git a/src/backend/optimizer/util/paramassign.c b/src/backend/optimizer/util/paramassign.c
new file mode 100644
index a58da7c..5242de4
--- a/src/backend/optimizer/util/paramassign.c
+++ b/src/backend/optimizer/util/paramassign.c
@@ -91,6 +91,7 @@ assign_param_for_var(PlannerInfo *root,
pvar->vartype == var->vartype &&
pvar->vartypmod == var->vartypmod &&
pvar->varcollid == var->varcollid &&
+ pvar->varreturningtype == var->varreturningtype &&
bms_equal(pvar->varnullingrels, var->varnullingrels))
return pitem->paramId;
}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
new file mode 100644
index 6bb53e4..167a0a5
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1809,8 +1809,8 @@ build_physical_tlist(PlannerInfo *root,
case RTE_NAMEDTUPLESTORE:
case RTE_RESULT:
/* Not all of these can have dropped cols, but share code anyway */
- expandRTE(rte, varno, 0, -1, true /* include dropped */ ,
- NULL, &colvars);
+ expandRTE(rte, varno, 0, VAR_RETURNING_DEFAULT, -1,
+ true /* include dropped */ , NULL, &colvars);
foreach(l, colvars)
{
var = (Var *) lfirst(l);
diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c
new file mode 100644
index 844fc30..3acc243
--- a/src/backend/optimizer/util/var.c
+++ b/src/backend/optimizer/util/var.c
@@ -75,6 +75,7 @@ static bool pull_varattnos_walker(Node *
static bool pull_vars_walker(Node *node, pull_vars_context *context);
static bool contain_var_clause_walker(Node *node, void *context);
static bool contain_vars_of_level_walker(Node *node, int *sublevels_up);
+static bool contain_vars_returning_old_or_new_walker(Node *node, void *context);
static bool locate_var_of_level_walker(Node *node,
locate_var_of_level_context *context);
static bool pull_var_clause_walker(Node *node,
@@ -490,6 +491,40 @@ contain_vars_of_level_walker(Node *node,
}
+/*
+ * contain_vars_returning_old_or_new
+ * Recursively scan a clause to discover whether it contains any Var nodes
+ * (of the current query level) whose varreturningtype is VAR_RETURNING_OLD
+ * or VAR_RETURNING_NEW.
+ *
+ * Returns true if any found.
+ *
+ * Does not examine subqueries, therefore must only be used after reduction
+ * of sublinks to subplans!
+ */
+bool
+contain_vars_returning_old_or_new(Node *node)
+{
+ return contain_vars_returning_old_or_new_walker(node, NULL);
+}
+
+static bool
+contain_vars_returning_old_or_new_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Var))
+ {
+ if (((Var *) node)->varlevelsup == 0 &&
+ ((Var *) node)->varreturningtype != VAR_RETURNING_DEFAULT)
+ return true; /* abort the tree traversal and return true */
+ return false;
+ }
+ return expression_tree_walker(node, contain_vars_returning_old_or_new_walker,
+ context);
+}
+
+
/*
* locate_var_of_level
* Find the parse location of any Var of the specified query level.
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
new file mode 100644
index 2255314..1903f34
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -72,7 +72,8 @@ static void determineRecursiveColTypes(P
Node *larg, List *nrtargetlist);
static Query *transformReturnStmt(ParseState *pstate, ReturnStmt *stmt);
static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
-static List *transformReturningList(ParseState *pstate, List *returningList);
+static void transformReturningClause(ParseState *pstate, Query *qry,
+ ReturningClause *returningClause);
static Query *transformPLAssignStmt(ParseState *pstate,
PLAssignStmt *stmt);
static Query *transformDeclareCursorStmt(ParseState *pstate,
@@ -551,7 +552,7 @@ transformDeleteStmt(ParseState *pstate,
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
- qry->returningList = transformReturningList(pstate, stmt->returningList);
+ transformReturningClause(pstate, qry, stmt->returningClause);
/* done building the range table and jointree */
qry->rtable = pstate->p_rtable;
@@ -963,7 +964,7 @@ transformInsertStmt(ParseState *pstate,
* contain only the target relation, removing any entries added in a
* sub-SELECT or VALUES list.
*/
- if (stmt->onConflictClause || stmt->returningList)
+ if (stmt->onConflictClause || stmt->returningClause)
{
pstate->p_namespace = NIL;
addNSItemToQuery(pstate, pstate->p_target_nsitem,
@@ -976,9 +977,8 @@ transformInsertStmt(ParseState *pstate,
stmt->onConflictClause);
/* Process RETURNING, if any. */
- if (stmt->returningList)
- qry->returningList = transformReturningList(pstate,
- stmt->returningList);
+ if (stmt->returningClause)
+ transformReturningClause(pstate, qry, stmt->returningClause);
/* done building the range table and jointree */
qry->rtable = pstate->p_rtable;
@@ -2443,7 +2443,7 @@ transformUpdateStmt(ParseState *pstate,
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
- qry->returningList = transformReturningList(pstate, stmt->returningList);
+ transformReturningClause(pstate, qry, stmt->returningClause);
/*
* Now we are done with SELECT-like processing, and can get on with
@@ -2539,17 +2539,115 @@ transformUpdateTargetList(ParseState *ps
}
/*
- * transformReturningList -
+ * buildNSItemForReturning -
+ * add a ParseNamespaceItem for the OLD or NEW alias in RETURNING.
+ */
+static void
+addNSItemForReturning(ParseState *pstate, const char *aliasname,
+ VarReturningType returning_type)
+{
+ List *colnames;
+ int numattrs;
+ ParseNamespaceColumn *nscolumns;
+ ParseNamespaceItem *nsitem;
+
+ /* copy per-column data from the target relation */
+ colnames = pstate->p_target_nsitem->p_rte->eref->colnames;
+ numattrs = list_length(colnames);
+
+ nscolumns = (ParseNamespaceColumn *)
+ palloc(numattrs * sizeof(ParseNamespaceColumn));
+
+ memcpy(nscolumns, pstate->p_target_nsitem->p_nscolumns,
+ numattrs * sizeof(ParseNamespaceColumn));
+
+ /* mark all columns as returning OLD/NEW */
+ for (int i = 0; i < numattrs; i++)
+ nscolumns[i].p_varreturningtype = returning_type;
+
+ /* build the nsitem, copying most fields from the target relation */
+ nsitem = (ParseNamespaceItem *) palloc(sizeof(ParseNamespaceItem));
+ nsitem->p_names = makeAlias(aliasname, colnames);
+ nsitem->p_rte = pstate->p_target_nsitem->p_rte;
+ nsitem->p_rtindex = pstate->p_target_nsitem->p_rtindex;
+ nsitem->p_perminfo = pstate->p_target_nsitem->p_perminfo;
+ nsitem->p_nscolumns = nscolumns;
+ nsitem->p_lateral_only = pstate->p_target_nsitem->p_lateral_only;
+ nsitem->p_lateral_ok = pstate->p_target_nsitem->p_lateral_ok;
+ nsitem->p_returning_type = returning_type;
+
+ /* add it to the query namespace as a table-only item */
+ addNSItemToQuery(pstate, nsitem, false, true, false);
+}
+
+/*
+ * transformReturningClause -
* handle a RETURNING clause in INSERT/UPDATE/DELETE
*/
-static List *
-transformReturningList(ParseState *pstate, List *returningList)
+static void
+transformReturningClause(ParseState *pstate, Query *qry,
+ ReturningClause *returningClause)
{
- List *rlist;
+ ListCell *lc;
int save_next_resno;
- if (returningList == NIL)
- return NIL; /* nothing to do */
+ if (returningClause == NULL)
+ return; /* nothing to do */
+
+ /*
+ * Scan RETURNING WITH(...) options for OLD/NEW alias names. Complain if
+ * there is any conflict with existing relations.
+ */
+ foreach(lc, returningClause->options)
+ {
+ ReturningOption *option = lfirst_node(ReturningOption, lc);
+
+ if (refnameNamespaceItem(pstate, NULL, option->name, -1, NULL))
+ ereport(ERROR,
+ errcode(ERRCODE_DUPLICATE_ALIAS),
+ errmsg("table name \"%s\" specified more than once",
+ option->name),
+ parser_errposition(pstate, option->location));
+
+ if (option->isNew)
+ {
+ if (qry->returningNew != NULL)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("NEW cannot be specified multiple times"),
+ parser_errposition(pstate, option->location));
+ qry->returningNew = option->name;
+ }
+ else
+ {
+ if (qry->returningOld != NULL)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("OLD cannot be specified multiple times"),
+ parser_errposition(pstate, option->location));
+ qry->returningOld = option->name;
+ }
+ }
+
+ /*
+ * If no OLD/NEW aliases specified, use "old"/"new" unless masked by
+ * existing relations.
+ */
+ if (qry->returningOld == NULL &&
+ refnameNamespaceItem(pstate, NULL, "old", -1, NULL) == NULL)
+ qry->returningOld = "old";
+ if (qry->returningNew == NULL &&
+ refnameNamespaceItem(pstate, NULL, "new", -1, NULL) == NULL)
+ qry->returningNew = "new";
+
+ /*
+ * Add the OLD and NEW aliases to the query namespace, for use in
+ * expressions in the RETURNING list.
+ */
+ if (qry->returningOld)
+ addNSItemForReturning(pstate, qry->returningOld, VAR_RETURNING_OLD);
+ if (qry->returningNew)
+ addNSItemForReturning(pstate, qry->returningNew, VAR_RETURNING_NEW);
/*
* We need to assign resnos starting at one in the RETURNING list. Save
@@ -2559,8 +2657,10 @@ transformReturningList(ParseState *pstat
save_next_resno = pstate->p_next_resno;
pstate->p_next_resno = 1;
- /* transform RETURNING identically to a SELECT targetlist */
- rlist = transformTargetList(pstate, returningList, EXPR_KIND_RETURNING);
+ /* transform RETURNING expressions identically to a SELECT targetlist */
+ qry->returningList = transformTargetList(pstate,
+ returningClause->exprs,
+ EXPR_KIND_RETURNING);
/*
* Complain if the nonempty tlist expanded to nothing (which is possible
@@ -2568,24 +2668,22 @@ transformReturningList(ParseState *pstat
* allow this, the parsed Query will look like it didn't have RETURNING,
* with results that would probably surprise the user.
*/
- if (rlist == NIL)
+ if (qry->returningList == NIL)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("RETURNING must have at least one column"),
parser_errposition(pstate,
- exprLocation(linitial(returningList)))));
+ exprLocation(linitial(returningClause->exprs)))));
/* mark column origins */
- markTargetListOrigins(pstate, rlist);
+ markTargetListOrigins(pstate, qry->returningList);
/* resolve any still-unresolved output columns as being type text */
if (pstate->p_resolve_unknowns)
- resolveTargetListUnknowns(pstate, rlist);
+ resolveTargetListUnknowns(pstate, qry->returningList);
/* restore state */
pstate->p_next_resno = save_next_resno;
-
- return rlist;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index c6e2f67..5ab94a6
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -278,6 +278,7 @@ static Node *makeRecursiveViewSelect(cha
MergeWhenClause *mergewhen;
struct KeyActions *keyactions;
struct KeyAction *keyaction;
+ ReturningClause *retclause;
}
%type stmt toplevel_stmt schema_stmt routine_body_stmt
@@ -447,7 +448,8 @@ static Node *makeRecursiveViewSelect(cha
opclass_purpose opt_opfamily transaction_mode_list_or_empty
OptTableFuncElementList TableFuncElementList opt_type_modifiers
prep_type_clause
- execute_param_clause using_clause returning_clause
+ execute_param_clause using_clause
+ returning_with_clause returning_options
opt_enum_val_list enum_val_list table_func_column_list
create_generic_options alter_generic_options
relation_expr_list dostmt_opt_list
@@ -456,6 +458,9 @@ static Node *makeRecursiveViewSelect(cha
vacuum_relation_list opt_vacuum_relation_list
drop_option_list pub_obj_list
+%type returning_clause
+%type returning_option
+%type returning_option_is_new
%type opt_routine_body
%type group_clause
%type group_by_list
@@ -12078,7 +12083,7 @@ InsertStmt:
{
$5->relation = $4;
$5->onConflictClause = $6;
- $5->returningList = $7;
+ $5->returningClause = $7;
$5->withClause = $1;
$$ = (Node *) $5;
}
@@ -12211,8 +12216,45 @@ opt_conf_expr:
;
returning_clause:
- RETURNING target_list { $$ = $2; }
- | /* EMPTY */ { $$ = NIL; }
+ RETURNING returning_with_clause target_list
+ {
+ ReturningClause *n = makeNode(ReturningClause);
+
+ n->options = $2;
+ n->exprs = $3;
+ $$ = n;
+ }
+ | /* EMPTY */
+ {
+ $$ = NULL;
+ }
+ ;
+
+returning_with_clause:
+ WITH '(' returning_options ')' { $$ = $3; }
+ | /* EMPTY */ { $$ = NIL; }
+ ;
+
+returning_options:
+ returning_option { $$ = list_make1($1); }
+ | returning_options ',' returning_option { $$ = lappend($1, $3); }
+ ;
+
+returning_option:
+ returning_option_is_new AS ColId
+ {
+ ReturningOption *n = makeNode(ReturningOption);
+
+ n->isNew = $1;
+ n->name = $3;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ ;
+
+returning_option_is_new:
+ OLD { $$ = false; }
+ | NEW { $$ = true; }
;
@@ -12231,7 +12273,7 @@ DeleteStmt: opt_with_clause DELETE_P FRO
n->relation = $4;
n->usingClause = $5;
n->whereClause = $6;
- n->returningList = $7;
+ n->returningClause = $7;
n->withClause = $1;
$$ = (Node *) n;
}
@@ -12305,7 +12347,7 @@ UpdateStmt: opt_with_clause UPDATE relat
n->targetList = $5;
n->fromClause = $6;
n->whereClause = $7;
- n->returningList = $8;
+ n->returningClause = $8;
n->withClause = $1;
$$ = (Node *) n;
}
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
new file mode 100644
index d2ac867..f6e1e63
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1579,6 +1579,7 @@ transformFromClauseItem(ParseState *psta
jnsitem->p_cols_visible = true;
jnsitem->p_lateral_only = false;
jnsitem->p_lateral_ok = true;
+ jnsitem->p_returning_type = VAR_RETURNING_DEFAULT;
/* Per SQL, we must check for alias conflicts */
checkNameSpaceConflicts(pstate, list_make1(jnsitem), my_namespace);
my_namespace = lappend(my_namespace, jnsitem);
@@ -1641,6 +1642,7 @@ buildVarFromNSColumn(ParseState *pstate,
nscol->p_varcollid,
0);
/* makeVar doesn't offer parameters for these, so set by hand: */
+ var->varreturningtype = nscol->p_varreturningtype;
var->varnosyn = nscol->p_varnosyn;
var->varattnosyn = nscol->p_varattnosyn;
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
new file mode 100644
index 9300c7b..c49d2c9
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -2574,6 +2574,13 @@ transformWholeRowRef(ParseState *pstate,
* point, there seems no harm in expanding it now rather than during
* planning.
*
+ * Note that if the nsitem is an OLD/NEW alias for the target RTE (as can
+ * appear in a RETURNING list), its alias won't match the target RTE's
+ * alias, but we still want to make a whole-row Var here rather than a
+ * RowExpr, for consistency with direct references to the target RTE, and
+ * so that any dropped columns are handled correctly. Thus we also check
+ * p_returning_type here.
+ *
* Note that if the RTE is a function returning scalar, we create just a
* plain reference to the function value, not a composite containing a
* single column. This is pretty inconsistent at first sight, but it's
@@ -2581,13 +2588,17 @@ transformWholeRowRef(ParseState *pstate,
* "rel.*" mean the same thing for composite relations, so why not for
* scalar functions...
*/
- if (nsitem->p_names == nsitem->p_rte->eref)
+ if (nsitem->p_names == nsitem->p_rte->eref ||
+ nsitem->p_returning_type != VAR_RETURNING_DEFAULT)
{
Var *result;
result = makeWholeRowVar(nsitem->p_rte, nsitem->p_rtindex,
sublevels_up, true);
+ /* mark Var for RETURNING OLD/NEW, as necessary */
+ result->varreturningtype = nsitem->p_returning_type;
+
/* location is not filled in by makeWholeRowVar */
result->location = location;
@@ -2610,9 +2621,8 @@ transformWholeRowRef(ParseState *pstate,
* are in the RTE. We needn't worry about marking the RTE for SELECT
* access, as the common columns are surely so marked already.
*/
- expandRTE(nsitem->p_rte, nsitem->p_rtindex,
- sublevels_up, location, false,
- NULL, &fields);
+ expandRTE(nsitem->p_rte, nsitem->p_rtindex, sublevels_up,
+ nsitem->p_returning_type, location, false, NULL, &fields);
rowexpr = makeNode(RowExpr);
rowexpr->args = list_truncate(fields,
list_length(nsitem->p_names->colnames));
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
new file mode 100644
index 6f5d9e2..c9a5a1e
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -91,11 +91,13 @@ static void markRTEForSelectPriv(ParseSt
int rtindex, AttrNumber col);
static void expandRelation(Oid relid, Alias *eref,
int rtindex, int sublevels_up,
+ VarReturningType returning_type,
int location, bool include_dropped,
List **colnames, List **colvars);
static void expandTupleDesc(TupleDesc tupdesc, Alias *eref,
int count, int offset,
int rtindex, int sublevels_up,
+ VarReturningType returning_type,
int location, bool include_dropped,
List **colnames, List **colvars);
static int specialAttNum(const char *attname);
@@ -763,6 +765,9 @@ scanNSItemForColumn(ParseState *pstate,
}
var->location = location;
+ /* Mark Var for RETURNING OLD/NEW, as necessary */
+ var->varreturningtype = nsitem->p_returning_type;
+
/* Mark Var if it's nulled by any outer joins */
markNullableIfNeeded(pstate, var);
@@ -1336,6 +1341,7 @@ buildNSItemFromTupleDesc(RangeTblEntry *
nsitem->p_cols_visible = true;
nsitem->p_lateral_only = false;
nsitem->p_lateral_ok = true;
+ nsitem->p_returning_type = VAR_RETURNING_DEFAULT;
return nsitem;
}
@@ -1399,6 +1405,7 @@ buildNSItemFromLists(RangeTblEntry *rte,
nsitem->p_cols_visible = true;
nsitem->p_lateral_only = false;
nsitem->p_lateral_ok = true;
+ nsitem->p_returning_type = VAR_RETURNING_DEFAULT;
return nsitem;
}
@@ -2300,6 +2307,7 @@ addRangeTableEntryForJoin(ParseState *ps
nsitem->p_cols_visible = true;
nsitem->p_lateral_only = false;
nsitem->p_lateral_ok = true;
+ nsitem->p_returning_type = VAR_RETURNING_DEFAULT;
return nsitem;
}
@@ -2646,9 +2654,10 @@ addNSItemToQuery(ParseState *pstate, Par
* results. If include_dropped is true then empty strings and NULL constants
* (not Vars!) are returned for dropped columns.
*
- * rtindex, sublevels_up, and location are the varno, varlevelsup, and location
- * values to use in the created Vars. Ordinarily rtindex should match the
- * actual position of the RTE in its rangetable.
+ * rtindex, sublevels_up, returning_type, and location are the varno,
+ * varlevelsup, varreturningtype, and location values to use in the created
+ * Vars. Ordinarily rtindex should match the actual position of the RTE in
+ * its rangetable.
*
* The output lists go into *colnames and *colvars.
* If only one of the two kinds of output list is needed, pass NULL for the
@@ -2656,6 +2665,7 @@ addNSItemToQuery(ParseState *pstate, Par
*/
void
expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up,
+ VarReturningType returning_type,
int location, bool include_dropped,
List **colnames, List **colvars)
{
@@ -2671,7 +2681,7 @@ expandRTE(RangeTblEntry *rte, int rtinde
case RTE_RELATION:
/* Ordinary relation RTE */
expandRelation(rte->relid, rte->eref,
- rtindex, sublevels_up, location,
+ rtindex, sublevels_up, returning_type, location,
include_dropped, colnames, colvars);
break;
case RTE_SUBQUERY:
@@ -2750,7 +2760,8 @@ expandRTE(RangeTblEntry *rte, int rtinde
Assert(tupdesc);
expandTupleDesc(tupdesc, rte->eref,
rtfunc->funccolcount, atts_done,
- rtindex, sublevels_up, location,
+ rtindex, sublevels_up,
+ returning_type, location,
include_dropped, colnames, colvars);
}
else if (functypclass == TYPEFUNC_SCALAR)
@@ -3009,6 +3020,7 @@ expandRTE(RangeTblEntry *rte, int rtinde
*/
static void
expandRelation(Oid relid, Alias *eref, int rtindex, int sublevels_up,
+ VarReturningType returning_type,
int location, bool include_dropped,
List **colnames, List **colvars)
{
@@ -3017,7 +3029,7 @@ expandRelation(Oid relid, Alias *eref, i
/* Get the tupledesc and turn it over to expandTupleDesc */
rel = relation_open(relid, AccessShareLock);
expandTupleDesc(rel->rd_att, eref, rel->rd_att->natts, 0,
- rtindex, sublevels_up,
+ rtindex, sublevels_up, returning_type,
location, include_dropped,
colnames, colvars);
relation_close(rel, AccessShareLock);
@@ -3035,6 +3047,7 @@ expandRelation(Oid relid, Alias *eref, i
static void
expandTupleDesc(TupleDesc tupdesc, Alias *eref, int count, int offset,
int rtindex, int sublevels_up,
+ VarReturningType returning_type,
int location, bool include_dropped,
List **colnames, List **colvars)
{
@@ -3095,6 +3108,7 @@ expandTupleDesc(TupleDesc tupdesc, Alias
attr->atttypid, attr->atttypmod,
attr->attcollation,
sublevels_up);
+ varnode->varreturningtype = returning_type;
varnode->location = location;
*colvars = lappend(*colvars, varnode);
@@ -3147,6 +3161,7 @@ expandNSItemVars(ParseState *pstate, Par
nscol->p_varcollid,
sublevels_up);
/* makeVar doesn't offer parameters for these, so set by hand: */
+ var->varreturningtype = nscol->p_varreturningtype;
var->varnosyn = nscol->p_varnosyn;
var->varattnosyn = nscol->p_varattnosyn;
var->location = location;
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
new file mode 100644
index f10fc42..e769bf4
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -1533,8 +1533,8 @@ expandRecordVariable(ParseState *pstate,
*lvar;
int i;
- expandRTE(rte, var->varno, 0, var->location, false,
- &names, &vars);
+ expandRTE(rte, var->varno, 0, var->varreturningtype,
+ var->location, false, &names, &vars);
tupleDesc = CreateTemplateTupleDesc(list_length(vars));
i = 1;
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
new file mode 100644
index 89187d9..3df87cf
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -662,15 +662,21 @@ rewriteRuleAction(Query *parsetree,
errmsg("cannot have RETURNING lists in multiple rules")));
*returning_flag = true;
rule_action->returningList = (List *)
- ReplaceVarsFromTargetList((Node *) parsetree->returningList,
- parsetree->resultRelation,
- 0,
- rt_fetch(parsetree->resultRelation,
- parsetree->rtable),
- rule_action->returningList,
- REPLACEVARS_REPORT_ERROR,
- 0,
- &rule_action->hasSubLinks);
+ ReplaceReturningVarsFromTargetList((Node *) parsetree->returningList,
+ parsetree->resultRelation,
+ 0,
+ rt_fetch(parsetree->resultRelation,
+ parsetree->rtable),
+ rule_action->returningList,
+ rule_action->resultRelation,
+ &rule_action->hasSubLinks);
+
+ /*
+ * Use the triggering query's aliases for OLD and NEW in the RETURNING
+ * list.
+ */
+ rule_action->returningOld = parsetree->returningOld;
+ rule_action->returningNew = parsetree->returningNew;
/*
* There could have been some SubLinks in parsetree's returningList,
@@ -3516,14 +3522,13 @@ rewriteTargetView(Query *parsetree, Rela
* reference the appropriate column of the base relation instead.
*/
parsetree = (Query *)
- ReplaceVarsFromTargetList((Node *) parsetree,
- parsetree->resultRelation,
- 0,
- view_rte,
- view_targetlist,
- REPLACEVARS_REPORT_ERROR,
- 0,
- NULL);
+ ReplaceReturningVarsFromTargetList((Node *) parsetree,
+ parsetree->resultRelation,
+ 0,
+ view_rte,
+ view_targetlist,
+ new_rt_index,
+ NULL);
/*
* Update all other RTI references in the query that point to the view
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
new file mode 100644
index 191f2dc..8b31c21
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -883,6 +883,68 @@ IncrementVarSublevelsUp_rtable(List *rta
QTW_EXAMINE_RTES_BEFORE);
}
+/*
+ * SetVarReturningType - adjust Vars by setting their returning type.
+ *
+ * Find all Var nodes referring to the specified result relation in the given
+ * expression and set their varreturningtype to the specified value.
+ *
+ * NOTE: although this has the form of a walker, we cheat and modify the
+ * Var nodes in-place. The given expression tree should have been copied
+ * earlier to ensure that no unwanted side-effects occur!
+ */
+
+typedef struct
+{
+ int result_relation;
+ int sublevels_up;
+ VarReturningType returning_type;
+} SetVarReturningType_context;
+
+static bool
+SetVarReturningType_walker(Node *node, SetVarReturningType_context *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+
+ if (var->varno == context->result_relation &&
+ var->varlevelsup == context->sublevels_up)
+ var->varreturningtype = context->returning_type;
+
+ return false;
+ }
+
+ if (IsA(node, Query))
+ {
+ /* Recurse into subselects */
+ bool result;
+
+ context->sublevels_up++;
+ result = query_tree_walker((Query *) node, SetVarReturningType_walker,
+ (void *) context, 0);
+ context->sublevels_up--;
+ return result;
+ }
+ return expression_tree_walker(node, SetVarReturningType_walker,
+ (void *) context);
+}
+
+static void
+SetVarReturningType(Node *node, int result_relation, int sublevels_up,
+ VarReturningType returning_type)
+{
+ SetVarReturningType_context context;
+
+ context.result_relation = result_relation;
+ context.sublevels_up = sublevels_up;
+ context.returning_type = returning_type;
+
+ /* Expect to start with an expression */
+ SetVarReturningType_walker(node, &context);
+}
/*
* rangeTableEntry_used - detect whether an RTE is referenced somewhere
@@ -1683,8 +1745,8 @@ ReplaceVarsFromTargetList_callback(Var *
* the RowExpr for use of the executor and ruleutils.c.
*/
expandRTE(rcon->target_rte,
- var->varno, var->varlevelsup, var->location,
- (var->vartype != RECORDOID),
+ var->varno, var->varlevelsup, VAR_RETURNING_DEFAULT,
+ var->location, (var->vartype != RECORDOID),
&colnames, &fields);
/* Adjust the generated per-field Vars... */
fields = (List *) replace_rte_variables_mutator((Node *) fields,
@@ -1786,3 +1848,58 @@ ReplaceVarsFromTargetList(Node *node,
(void *) &context,
outer_hasSubLinks);
}
+
+
+/*
+ * ReplaceReturningVarsFromTargetList -
+ * replace RETURNING list Vars with items from a targetlist
+ *
+ * This is equivalent to calling ReplaceVarsFromTargetList() with a
+ * nomatch_option of REPLACEVARS_REPORT_ERROR, but with the added effect of
+ * copying varreturningtype onto any Vars referring to new_result_relation,
+ * allowing RETURNING OLD/NEW to work in the rewritten query.
+ */
+
+typedef struct
+{
+ ReplaceVarsFromTargetList_context rv_con;
+ int new_result_relation;
+} ReplaceReturningVarsFromTargetList_context;
+
+static Node *
+ReplaceReturningVarsFromTargetList_callback(Var *var,
+ replace_rte_variables_context *context)
+{
+ ReplaceReturningVarsFromTargetList_context *rcon = (ReplaceReturningVarsFromTargetList_context *) context->callback_arg;
+ Node *newnode;
+
+ newnode = ReplaceVarsFromTargetList_callback(var, context);
+
+ if (var->varreturningtype != VAR_RETURNING_DEFAULT)
+ SetVarReturningType((Node *) newnode, rcon->new_result_relation,
+ var->varlevelsup, var->varreturningtype);
+
+ return newnode;
+}
+
+Node *
+ReplaceReturningVarsFromTargetList(Node *node,
+ int target_varno, int sublevels_up,
+ RangeTblEntry *target_rte,
+ List *targetlist,
+ int new_result_relation,
+ bool *outer_hasSubLinks)
+{
+ ReplaceReturningVarsFromTargetList_context context;
+
+ context.rv_con.target_rte = target_rte;
+ context.rv_con.targetlist = targetlist;
+ context.rv_con.nomatch_option = REPLACEVARS_REPORT_ERROR;
+ context.rv_con.nomatch_varno = 0;
+ context.new_result_relation = new_result_relation;
+
+ return replace_rte_variables(node, target_varno, sublevels_up,
+ ReplaceReturningVarsFromTargetList_callback,
+ (void *) &context,
+ outer_hasSubLinks);
+}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
new file mode 100644
index 2a1ee69..2035c0f
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -166,6 +166,8 @@ typedef struct
List *subplans; /* List of Plan trees for SubPlans */
List *ctes; /* List of CommonTableExpr nodes */
AppendRelInfo **appendrels; /* Array of AppendRelInfo nodes, or NULL */
+ char *returningOld; /* alias for OLD in RETURNING list */
+ char *returningNew; /* alias for NEW in RETURNING list */
/* Workspace for column alias assignment: */
bool unique_using; /* Are we making USING names globally unique */
List *using_names; /* List of assigned names for USING columns */
@@ -416,6 +418,8 @@ static void get_basic_select_query(Query
TupleDesc resultDesc, bool colNamesVisible);
static void get_target_list(List *targetList, deparse_context *context,
TupleDesc resultDesc, bool colNamesVisible);
+static void get_returning_clause(Query *query, deparse_context *context,
+ bool colNamesVisible);
static void get_setop_query(Node *setOp, Query *query,
deparse_context *context,
TupleDesc resultDesc, bool colNamesVisible);
@@ -3782,6 +3786,13 @@ set_deparse_context_plan(List *dpcontext
dpns->ancestors = ancestors;
set_deparse_plan(dpns, plan);
+ /* For ModifyTable, set aliases for OLD and NEW in RETURNING */
+ if (IsA(plan, ModifyTable))
+ {
+ dpns->returningOld = ((ModifyTable *) plan)->returningOld;
+ dpns->returningNew = ((ModifyTable *) plan)->returningNew;
+ }
+
return dpcontext;
}
@@ -3979,6 +3990,8 @@ set_deparse_for_query(deparse_namespace
dpns->subplans = NIL;
dpns->ctes = query->cteList;
dpns->appendrels = NULL;
+ dpns->returningOld = query->returningOld;
+ dpns->returningNew = query->returningNew;
/* Assign a unique relation alias to each RTE */
set_rtable_names(dpns, parent_namespaces, NULL);
@@ -4366,8 +4379,8 @@ set_relation_column_names(deparse_namesp
if (rte->rtekind == RTE_FUNCTION && rte->functions != NIL)
{
/* Since we're not creating Vars, rtindex etc. don't matter */
- expandRTE(rte, 1, 0, -1, true /* include dropped */ ,
- &colnames, NULL);
+ expandRTE(rte, 1, 0, VAR_RETURNING_DEFAULT, -1,
+ true /* include dropped */ , &colnames, NULL);
}
else
colnames = rte->eref->colnames;
@@ -6156,6 +6169,44 @@ get_target_list(List *targetList, depars
}
static void
+get_returning_clause(Query *query, deparse_context *context,
+ bool colNamesVisible)
+{
+ StringInfo buf = context->buf;
+
+ if (query->returningList)
+ {
+ bool have_with = false;
+
+ appendContextKeyword(context, " RETURNING",
+ -PRETTYINDENT_STD, PRETTYINDENT_STD, 1);
+
+ /* Add WITH options, if they're not the defaults */
+ if (query->returningOld && strcmp(query->returningOld, "old") != 0)
+ {
+ appendStringInfo(buf, " WITH (OLD AS %s", query->returningOld);
+ have_with = true;
+ }
+ if (query->returningNew && strcmp(query->returningNew, "new") != 0)
+ {
+ if (have_with)
+ appendStringInfo(buf, ", ");
+ else
+ {
+ appendStringInfo(buf, " WITH (");
+ have_with = true;
+ }
+ appendStringInfo(buf, "NEW AS %s", query->returningNew);
+ }
+ if (have_with)
+ appendStringInfo(buf, ")");
+
+ /* Add the returning expressions themselves */
+ get_target_list(query->returningList, context, NULL, colNamesVisible);
+ }
+}
+
+static void
get_setop_query(Node *setOp, Query *query, deparse_context *context,
TupleDesc resultDesc, bool colNamesVisible)
{
@@ -6809,12 +6860,7 @@ get_insert_query_def(Query *query, depar
}
/* Add RETURNING if present */
- if (query->returningList)
- {
- appendContextKeyword(context, " RETURNING",
- -PRETTYINDENT_STD, PRETTYINDENT_STD, 1);
- get_target_list(query->returningList, context, NULL, colNamesVisible);
- }
+ get_returning_clause(query, context, colNamesVisible);
}
@@ -6866,12 +6912,7 @@ get_update_query_def(Query *query, depar
}
/* Add RETURNING if present */
- if (query->returningList)
- {
- appendContextKeyword(context, " RETURNING",
- -PRETTYINDENT_STD, PRETTYINDENT_STD, 1);
- get_target_list(query->returningList, context, NULL, colNamesVisible);
- }
+ get_returning_clause(query, context, colNamesVisible);
}
@@ -7070,12 +7111,7 @@ get_delete_query_def(Query *query, depar
}
/* Add RETURNING if present */
- if (query->returningList)
- {
- appendContextKeyword(context, " RETURNING",
- -PRETTYINDENT_STD, PRETTYINDENT_STD, 1);
- get_target_list(query->returningList, context, NULL, colNamesVisible);
- }
+ get_returning_clause(query, context, colNamesVisible);
}
@@ -7344,7 +7380,13 @@ get_variable(Var *var, int levelsup, boo
}
rte = rt_fetch(varno, dpns->rtable);
- refname = (char *) list_nth(dpns->rtable_names, varno - 1);
+ if (var->varreturningtype == VAR_RETURNING_OLD)
+ refname = dpns->returningOld;
+ else if (var->varreturningtype == VAR_RETURNING_NEW)
+ refname = dpns->returningNew;
+ else
+ refname = (char *) list_nth(dpns->rtable_names, varno - 1);
+
colinfo = deparse_columns_fetch(varno, dpns);
attnum = varattno;
}
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
new file mode 100644
index a28ddcd..fb937d4
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -26,9 +26,9 @@ struct JsonConstructorExprState;
/* Bits in ExprState->flags (see also execnodes.h for public flag bits): */
/* expression's interpreter has been initialized */
-#define EEO_FLAG_INTERPRETER_INITIALIZED (1 << 1)
+#define EEO_FLAG_INTERPRETER_INITIALIZED (1 << 3)
/* jump-threading is in use */
-#define EEO_FLAG_DIRECT_THREADED (1 << 2)
+#define EEO_FLAG_DIRECT_THREADED (1 << 4)
/* Typical API for out-of-line evaluation subroutines */
typedef void (*ExecEvalSubroutine) (ExprState *state,
@@ -72,16 +72,22 @@ typedef enum ExprEvalOp
EEOP_INNER_FETCHSOME,
EEOP_OUTER_FETCHSOME,
EEOP_SCAN_FETCHSOME,
+ EEOP_OLD_FETCHSOME,
+ EEOP_NEW_FETCHSOME,
/* compute non-system Var value */
EEOP_INNER_VAR,
EEOP_OUTER_VAR,
EEOP_SCAN_VAR,
+ EEOP_OLD_VAR,
+ EEOP_NEW_VAR,
/* compute system Var value */
EEOP_INNER_SYSVAR,
EEOP_OUTER_SYSVAR,
EEOP_SCAN_SYSVAR,
+ EEOP_OLD_SYSVAR,
+ EEOP_NEW_SYSVAR,
/* compute wholerow Var */
EEOP_WHOLEROW,
@@ -94,6 +100,8 @@ typedef enum ExprEvalOp
EEOP_ASSIGN_INNER_VAR,
EEOP_ASSIGN_OUTER_VAR,
EEOP_ASSIGN_SCAN_VAR,
+ EEOP_ASSIGN_OLD_VAR,
+ EEOP_ASSIGN_NEW_VAR,
/* assign ExprState's resvalue/resnull to a column of its resultslot */
EEOP_ASSIGN_TMP,
diff --git a/src/include/executor/tuptable.h b/src/include/executor/tuptable.h
new file mode 100644
index 6133dbc..c9d3661
--- a/src/include/executor/tuptable.h
+++ b/src/include/executor/tuptable.h
@@ -411,12 +411,21 @@ slot_getsysattr(TupleTableSlot *slot, in
{
Assert(attnum < 0); /* caller error */
+ /*
+ * If the tid is not valid, there is no physical row, and all system
+ * attributes are deemed to be NULL, except for the tableoid.
+ */
if (attnum == TableOidAttributeNumber)
{
*isnull = false;
return ObjectIdGetDatum(slot->tts_tableOid);
}
- else if (attnum == SelfItemPointerAttributeNumber)
+ if (!ItemPointerIsValid(&slot->tts_tid))
+ {
+ *isnull = true;
+ return PointerGetDatum(NULL);
+ }
+ if (attnum == SelfItemPointerAttributeNumber)
{
*isnull = false;
return PointerGetDatum(&slot->tts_tid);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
new file mode 100644
index 444a5f0..b005501
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -74,6 +74,10 @@ typedef Datum (*ExprStateEvalFunc) (stru
/* Bits in ExprState->flags (see also execExpr.h for private flag bits): */
/* expression is for use with ExecQual() */
#define EEO_FLAG_IS_QUAL (1 << 0)
+/* expression refers to OLD table columns (used in RETURNING lists) */
+#define EEO_FLAG_HAS_OLD (1 << 1)
+/* expression refers to NEW table columns (used in RETURNING lists) */
+#define EEO_FLAG_HAS_NEW (1 << 2)
typedef struct ExprState
{
@@ -287,6 +291,12 @@ typedef struct ExprContext
#define FIELDNO_EXPRCONTEXT_DOMAINNULL 13
bool domainValue_isNull;
+ /* Tuples that OLD/NEW Var nodes in RETURNING may refer to */
+#define FIELDNO_EXPRCONTEXT_OLDTUPLE 14
+ TupleTableSlot *ecxt_oldtuple;
+#define FIELDNO_EXPRCONTEXT_NEWTUPLE 15
+ TupleTableSlot *ecxt_newtuple;
+
/* Link to containing EState (NULL if a standalone ExprContext) */
struct EState *ecxt_estate;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
new file mode 100644
index 2380821..2f69183
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -193,6 +193,8 @@ typedef struct Query
OnConflictExpr *onConflict; /* ON CONFLICT DO [NOTHING | UPDATE] */
+ char *returningOld; /* alias for OLD in RETURNING list */
+ char *returningNew; /* alias for NEW in RETURNING list */
List *returningList; /* return-values list (of TargetEntry) */
List *groupClause; /* a list of SortGroupClause's */
@@ -1686,6 +1688,32 @@ typedef struct MergeWhenClause
} MergeWhenClause;
/*
+ * ReturningOption -
+ * Option in RETURNING WITH(...) list
+ *
+ * Currently, this is used only for specifying the OLD/NEW aliases available
+ * for use in the RETURNING expression list.
+ */
+typedef struct ReturningOption
+{
+ NodeTag type;
+ bool isNew;
+ char *name;
+ int location;
+} ReturningOption;
+
+/*
+ * ReturningClause -
+ * List of RETURNING expressions, together with any WITH(...) options
+ */
+typedef struct ReturningClause
+{
+ NodeTag type;
+ List *options; /* list of ReturningOption elements */
+ List *exprs; /* list of expressions to return */
+} ReturningClause;
+
+/*
* TriggerTransition -
* representation of transition row or table naming clause
*
@@ -1893,7 +1921,7 @@ typedef struct InsertStmt
List *cols; /* optional: names of the target columns */
Node *selectStmt; /* the source SELECT/VALUES, or NULL */
OnConflictClause *onConflictClause; /* ON CONFLICT clause */
- List *returningList; /* list of expressions to return */
+ ReturningClause *returningClause; /* RETURNING clause */
WithClause *withClause; /* WITH clause */
OverridingKind override; /* OVERRIDING clause */
} InsertStmt;
@@ -1908,7 +1936,7 @@ typedef struct DeleteStmt
RangeVar *relation; /* relation to delete from */
List *usingClause; /* optional using clause for more tables */
Node *whereClause; /* qualifications */
- List *returningList; /* list of expressions to return */
+ ReturningClause *returningClause; /* RETURNING clause */
WithClause *withClause; /* WITH clause */
} DeleteStmt;
@@ -1923,7 +1951,7 @@ typedef struct UpdateStmt
List *targetList; /* the target list (of ResTarget) */
Node *whereClause; /* qualifications */
List *fromClause; /* optional from clause for more tables */
- List *returningList; /* list of expressions to return */
+ ReturningClause *returningClause; /* RETURNING clause */
WithClause *withClause; /* WITH clause */
} UpdateStmt;
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
new file mode 100644
index b4ef6bc..8557cd8
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -237,6 +237,8 @@ typedef struct ModifyTable
List *resultRelations; /* integer list of RT indexes */
List *updateColnosLists; /* per-target-table update_colnos lists */
List *withCheckOptionLists; /* per-target-table WCO lists */
+ char *returningOld; /* alias for OLD in RETURNING lists */
+ char *returningNew; /* alias for NEW in RETURNING lists */
List *returningLists; /* per-target-table RETURNING tlists */
List *fdwPrivLists; /* per-target-table FDW private data lists */
Bitmapset *fdwDirectModifyPlans; /* indices of FDW DM plans */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
new file mode 100644
index 4a15460..7afc663
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -209,6 +209,11 @@ typedef struct Expr
* Note that it affects the meaning of all of varno, varnullingrels, and
* varnosyn, all of which refer to the range table of that query level.
*
+ * varreturningtype is used for Vars in the RETURNING list of data-modifying
+ * queries, for Vars that refer to the target relation. For such Vars, there
+ * are 3 possible behaviors, depending on whether the target relation was
+ * referred to directly, or via the OLD or NEW aliases.
+ *
* In the parser, varnosyn and varattnosyn are either identical to
* varno/varattno, or they specify the column's position in an aliased JOIN
* RTE that hides the semantic referent RTE's refname. This is a syntactic
@@ -230,6 +235,14 @@ typedef struct Expr
#define PRS2_OLD_VARNO 1
#define PRS2_NEW_VARNO 2
+/* Returning behavior for Vars in RETURNING list */
+typedef enum VarReturningType
+{
+ VAR_RETURNING_DEFAULT, /* return OLD for DELETE, else return NEW */
+ VAR_RETURNING_OLD, /* return OLD for DELETE/UPDATE, else NULL */
+ VAR_RETURNING_NEW, /* return NEW for INSERT/UPDATE, else NULL */
+} VarReturningType;
+
typedef struct Var
{
Expr xpr;
@@ -265,6 +278,9 @@ typedef struct Var
*/
Index varlevelsup;
+ /* returning type of this var (see above) */
+ VarReturningType varreturningtype;
+
/*
* varnosyn/varattnosyn are ignored for equality, because Vars with
* different syntactic identifiers are semantically the same as long as
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
new file mode 100644
index 7b63c5c..be1fa41
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -198,6 +198,7 @@ extern void pull_varattnos(Node *node, I
extern List *pull_vars_of_level(Node *node, int levelsup);
extern bool contain_var_clause(Node *node);
extern bool contain_vars_of_level(Node *node, int levelsup);
+extern bool contain_vars_returning_old_or_new(Node *node);
extern int locate_var_of_level(Node *node, int levelsup);
extern List *pull_var_clause(Node *node, int flags);
extern Node *flatten_join_alias_vars(PlannerInfo *root, Query *query, Node *node);
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
new file mode 100644
index 99d6515..669a644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -275,6 +275,11 @@ struct ParseState
* of SQL:2008 requires us to do it this way. We also use p_lateral_ok to
* forbid LATERAL references to an UPDATE/DELETE target table.
*
+ * While processing the RETURNING clause, special namespace items are added to
+ * refer to the OLD and NEW state of the result relation. These namespace
+ * items have p_returning_type set appropriately, for use when creating Vars.
+ * For convenience, this information is duplicated on each namespace column.
+ *
* At no time should a namespace list contain two entries that conflict
* according to the rules in checkNameSpaceConflicts; but note that those
* are more complicated than "must have different alias names", so in practice
@@ -292,6 +297,7 @@ struct ParseNamespaceItem
bool p_cols_visible; /* Column names visible as unqualified refs? */
bool p_lateral_only; /* Is only visible to LATERAL expressions? */
bool p_lateral_ok; /* If so, does join type allow use? */
+ VarReturningType p_returning_type; /* Is OLD/NEW for use in RETURNING? */
};
/*
@@ -322,6 +328,7 @@ struct ParseNamespaceColumn
Oid p_vartype; /* pg_type OID */
int32 p_vartypmod; /* type modifier value */
Oid p_varcollid; /* OID of collation, or InvalidOid */
+ VarReturningType p_varreturningtype; /* for RETURNING OLD/NEW */
Index p_varnosyn; /* rangetable index of syntactic referent */
AttrNumber p_varattnosyn; /* attribute number of syntactic referent */
bool p_dontexpand; /* not included in star expansion */
diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h
new file mode 100644
index bea2da5..20f7677
--- a/src/include/parser/parse_relation.h
+++ b/src/include/parser/parse_relation.h
@@ -112,6 +112,7 @@ extern void errorMissingRTE(ParseState *
extern void errorMissingColumn(ParseState *pstate,
const char *relname, const char *colname, int location) pg_attribute_noreturn();
extern void expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up,
+ VarReturningType returning_type,
int location, bool include_dropped,
List **colnames, List **colvars);
extern List *expandNSItemVars(ParseState *pstate, ParseNamespaceItem *nsitem,
diff --git a/src/include/rewrite/rewriteManip.h b/src/include/rewrite/rewriteManip.h
new file mode 100644
index ac6d204..1fde35f
--- a/src/include/rewrite/rewriteManip.h
+++ b/src/include/rewrite/rewriteManip.h
@@ -93,4 +93,12 @@ extern Node *ReplaceVarsFromTargetList(N
int nomatch_varno,
bool *outer_hasSubLinks);
+extern Node *ReplaceReturningVarsFromTargetList(Node *node,
+ int target_varno,
+ int sublevels_up,
+ RangeTblEntry *target_rte,
+ List *targetlist,
+ int new_result_relation,
+ bool *outer_hasSubLinks);
+
#endif /* REWRITEMANIP_H */
diff --git a/src/interfaces/ecpg/preproc/parse.pl b/src/interfaces/ecpg/preproc/parse.pl
new file mode 100644
index 87b512b..44fc01b
--- a/src/interfaces/ecpg/preproc/parse.pl
+++ b/src/interfaces/ecpg/preproc/parse.pl
@@ -118,8 +118,8 @@ my %replace_line = (
'SHOW TRANSACTION ISOLATION LEVEL ecpg_into',
'VariableShowStmtSHOWSESSIONAUTHORIZATION' =>
'SHOW SESSION AUTHORIZATION ecpg_into',
- 'returning_clauseRETURNINGtarget_list' =>
- 'RETURNING target_list opt_ecpg_into',
+ 'returning_clauseRETURNINGreturning_with_clausetarget_list' =>
+ 'RETURNING returning_with_clause target_list opt_ecpg_into',
'ExecuteStmtEXECUTEnameexecute_param_clause' =>
'EXECUTE prepared_name execute_param_clause execute_rest',
'ExecuteStmtCREATEOptTempTABLEcreate_as_targetASEXECUTEnameexecute_param_clauseopt_with_data'
diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out
new file mode 100644
index cb51bb8..20a4f65
--- a/src/test/regress/expected/returning.out
+++ b/src/test/regress/expected/returning.out
@@ -355,3 +355,511 @@ INSERT INTO foo AS bar DEFAULT VALUES RE
42
(1 row)
+--
+-- Test RETURNING OLD/NEW.
+--
+-- Start with new data, to ensure predictable TIDs.
+--
+TRUNCATE foo;
+INSERT INTO foo VALUES (1, 'xxx', 10, 20), (2, 'more', 42, 141), (3, 'zoo2', 57, 99);
+-- Error cases
+INSERT INTO foo DEFAULT VALUES RETURNING WITH (nonsuch AS something) *;
+ERROR: syntax error at or near "nonsuch"
+LINE 1: INSERT INTO foo DEFAULT VALUES RETURNING WITH (nonsuch AS so...
+ ^
+INSERT INTO foo DEFAULT VALUES RETURNING WITH (new AS foo) *;
+ERROR: table name "foo" specified more than once
+LINE 1: INSERT INTO foo DEFAULT VALUES RETURNING WITH (new AS foo) *...
+ ^
+INSERT INTO foo DEFAULT VALUES RETURNING WITH (old AS o, new AS n, old AS o) *;
+ERROR: OLD cannot be specified multiple times
+LINE 1: ...EFAULT VALUES RETURNING WITH (old AS o, new AS n, old AS o) ...
+ ^
+-- INSERT has NEW, but not OLD
+EXPLAIN (verbose, costs off)
+INSERT INTO foo VALUES (4)
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Insert on pg_temp.foo
+ Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, foo.f1, foo.f2, foo.f3, foo.f4
+ -> Result
+ Output: 4, NULL::text, 42, '99'::bigint
+(4 rows)
+
+INSERT INTO foo VALUES (4)
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+ tableoid | ctid | f1 | f2 | f3 | f4 | tableoid | ctid | f1 | f2 | f3 | f4 | f1 | f2 | f3 | f4
+----------+------+----+----+----+----+----------+-------+----+----+----+----+----+----+----+----
+ foo | | | | | | foo | (0,4) | 4 | | 42 | 99 | 4 | | 42 | 99
+(1 row)
+
+-- INSERT ... ON CONFLICT ... UPDATE has OLD and NEW
+CREATE UNIQUE INDEX foo_f1_idx ON foo (f1);
+EXPLAIN (verbose, costs off)
+INSERT INTO foo VALUES (4, 'conflict'), (5, 'ok')
+ ON CONFLICT (f1) DO UPDATE SET f2 = excluded.f2||'ed', f3 = -1
+ RETURNING WITH (OLD AS o, NEW AS n)
+ o.tableoid::regclass, o.ctid, o.*,
+ n.tableoid::regclass, n.ctid, n.*, *;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------
+ Insert on pg_temp.foo
+ Output: (o.tableoid)::regclass, o.ctid, o.f1, o.f2, o.f3, o.f4, (n.tableoid)::regclass, n.ctid, n.f1, n.f2, n.f3, n.f4, foo.f1, foo.f2, foo.f3, foo.f4
+ Conflict Resolution: UPDATE
+ Conflict Arbiter Indexes: foo_f1_idx
+ -> Values Scan on "*VALUES*"
+ Output: "*VALUES*".column1, "*VALUES*".column2, 42, '99'::bigint
+(6 rows)
+
+INSERT INTO foo VALUES (4, 'conflict'), (5, 'ok')
+ ON CONFLICT (f1) DO UPDATE SET f2 = excluded.f2||'ed', f3 = -1
+ RETURNING WITH (OLD AS o, NEW AS n)
+ o.tableoid::regclass, o.ctid, o.*,
+ n.tableoid::regclass, n.ctid, n.*, *;
+ tableoid | ctid | f1 | f2 | f3 | f4 | tableoid | ctid | f1 | f2 | f3 | f4 | f1 | f2 | f3 | f4
+----------+-------+----+----+----+----+----------+-------+----+------------+----+----+----+------------+----+----
+ foo | (0,4) | 4 | | 42 | 99 | foo | (0,5) | 4 | conflicted | -1 | 99 | 4 | conflicted | -1 | 99
+ foo | | | | | | foo | (0,6) | 5 | ok | 42 | 99 | 5 | ok | 42 | 99
+(2 rows)
+
+-- UPDATE has OLD and NEW
+EXPLAIN (verbose, costs off)
+UPDATE foo SET f4 = 100 WHERE f1 = 5
+ RETURNING old.tableoid::regclass, old.ctid, old.*, old,
+ new.tableoid::regclass, new.ctid, new.*, new,
+ old.f4::text||'->'||new.f4::text AS change;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on pg_temp.foo
+ Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, old.*, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, new.*, (((old.f4)::text || '->'::text) || (new.f4)::text)
+ Update on pg_temp.foo foo_1
+ -> Result
+ Output: '100'::bigint, foo_1.tableoid, foo_1.ctid
+ -> Seq Scan on pg_temp.foo foo_1
+ Output: foo_1.tableoid, foo_1.ctid
+ Filter: (foo_1.f1 = 5)
+(8 rows)
+
+UPDATE foo SET f4 = 100 WHERE f1 = 5
+ RETURNING old.tableoid::regclass, old.ctid, old.*, old,
+ new.tableoid::regclass, new.ctid, new.*, new,
+ old.f4::text||'->'||new.f4::text AS change;
+ tableoid | ctid | f1 | f2 | f3 | f4 | old | tableoid | ctid | f1 | f2 | f3 | f4 | new | change
+----------+-------+----+----+----+----+--------------+----------+-------+----+----+----+-----+---------------+---------
+ foo | (0,6) | 5 | ok | 42 | 99 | (5,ok,42,99) | foo | (0,7) | 5 | ok | 42 | 100 | (5,ok,42,100) | 99->100
+(1 row)
+
+-- DELETE has OLD, but not NEW
+EXPLAIN (verbose, costs off)
+DELETE FROM foo WHERE f1 = 5
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Delete on pg_temp.foo
+ Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, foo_1.f1, foo_1.f2, foo_1.f3, foo_1.f4
+ Delete on pg_temp.foo foo_1
+ -> Seq Scan on pg_temp.foo foo_1
+ Output: foo_1.tableoid, foo_1.ctid
+ Filter: (foo_1.f1 = 5)
+(6 rows)
+
+DELETE FROM foo WHERE f1 = 5
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+ tableoid | ctid | f1 | f2 | f3 | f4 | tableoid | ctid | f1 | f2 | f3 | f4 | f1 | f2 | f3 | f4
+----------+-------+----+----+----+-----+----------+------+----+----+----+----+----+----+----+-----
+ foo | (0,7) | 5 | ok | 42 | 100 | foo | | | | | | 5 | ok | 42 | 100
+(1 row)
+
+-- RETURNING OLD and NEW from subquery
+EXPLAIN (verbose, costs off)
+INSERT INTO foo VALUES (5, 'subquery test')
+ RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max,
+ (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
+ QUERY PLAN
+---------------------------------------------------------------
+ Insert on pg_temp.foo
+ Output: (SubPlan 1), (SubPlan 2)
+ -> Result
+ Output: 5, 'subquery test'::text, 42, '99'::bigint
+ SubPlan 1
+ -> Aggregate
+ Output: max((old.f4 + x.x))
+ -> Function Scan on pg_catalog.generate_series x
+ Output: x.x
+ Function Call: generate_series(1, 10)
+ SubPlan 2
+ -> Aggregate
+ Output: max((new.f4 + x_1.x))
+ -> Function Scan on pg_catalog.generate_series x_1
+ Output: x_1.x
+ Function Call: generate_series(1, 10)
+(16 rows)
+
+INSERT INTO foo VALUES (5, 'subquery test')
+ RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max,
+ (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
+ old_max | new_max
+---------+---------
+ | 109
+(1 row)
+
+EXPLAIN (verbose, costs off)
+UPDATE foo SET f4 = 100 WHERE f1 = 5
+ RETURNING (SELECT old.f4 = new.f4),
+ (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max,
+ (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
+ QUERY PLAN
+---------------------------------------------------------------
+ Update on pg_temp.foo
+ Output: (SubPlan 1), (SubPlan 2), (SubPlan 3)
+ Update on pg_temp.foo foo_1
+ -> Result
+ Output: '100'::bigint, foo_1.tableoid, foo_1.ctid
+ -> Seq Scan on pg_temp.foo foo_1
+ Output: foo_1.tableoid, foo_1.ctid
+ Filter: (foo_1.f1 = 5)
+ SubPlan 1
+ -> Result
+ Output: (old.f4 = new.f4)
+ SubPlan 2
+ -> Aggregate
+ Output: max((old.f4 + x.x))
+ -> Function Scan on pg_catalog.generate_series x
+ Output: x.x
+ Function Call: generate_series(1, 10)
+ SubPlan 3
+ -> Aggregate
+ Output: max((new.f4 + x_1.x))
+ -> Function Scan on pg_catalog.generate_series x_1
+ Output: x_1.x
+ Function Call: generate_series(1, 10)
+(23 rows)
+
+UPDATE foo SET f4 = 100 WHERE f1 = 5
+ RETURNING (SELECT old.f4 = new.f4),
+ (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max,
+ (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
+ ?column? | old_max | new_max
+----------+---------+---------
+ f | 109 | 110
+(1 row)
+
+EXPLAIN (verbose, costs off)
+DELETE FROM foo WHERE f1 = 5
+ RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max,
+ (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
+ QUERY PLAN
+---------------------------------------------------------------
+ Delete on pg_temp.foo
+ Output: (SubPlan 1), (SubPlan 2)
+ Delete on pg_temp.foo foo_1
+ -> Seq Scan on pg_temp.foo foo_1
+ Output: foo_1.tableoid, foo_1.ctid
+ Filter: (foo_1.f1 = 5)
+ SubPlan 1
+ -> Aggregate
+ Output: max((old.f4 + x.x))
+ -> Function Scan on pg_catalog.generate_series x
+ Output: x.x
+ Function Call: generate_series(1, 10)
+ SubPlan 2
+ -> Aggregate
+ Output: max((new.f4 + x_1.x))
+ -> Function Scan on pg_catalog.generate_series x_1
+ Output: x_1.x
+ Function Call: generate_series(1, 10)
+(18 rows)
+
+DELETE FROM foo WHERE f1 = 5
+ RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max,
+ (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
+ old_max | new_max
+---------+---------
+ 110 |
+(1 row)
+
+-- DELETE turned into UPDATE by a rule has OLD and NEW
+CREATE RULE foo_del_rule AS ON DELETE TO foo DO INSTEAD
+ UPDATE foo SET f2 = f2||' (deleted)', f3 = -1, f4 = -1 WHERE f1 = OLD.f1
+ RETURNING *;
+EXPLAIN (verbose, costs off)
+DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------
+ Update on pg_temp.foo
+ Output: old.f1, old.f2, old.f3, old.f4, new.f1, new.f2, new.f3, new.f4, foo_2.f1, foo_2.f2, foo_2.f3, foo_2.f4
+ Update on pg_temp.foo foo_2
+ -> Nested Loop
+ Output: (foo_2.f2 || ' (deleted)'::text), '-1'::integer, '-1'::bigint, foo_1.ctid, foo_1.tableoid, foo_2.tableoid, foo_2.ctid
+ -> Seq Scan on pg_temp.foo foo_2
+ Output: foo_2.f2, foo_2.f1, foo_2.tableoid, foo_2.ctid
+ Filter: (foo_2.f1 = 4)
+ -> Seq Scan on pg_temp.foo foo_1
+ Output: foo_1.ctid, foo_1.f1, foo_1.tableoid
+ Filter: (foo_1.f1 = 4)
+(11 rows)
+
+DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *;
+ f1 | f2 | f3 | f4 | f1 | f2 | f3 | f4 | f1 | f2 | f3 | f4
+----+------------+----+----+----+----------------------+----+----+----+----------------------+----+----
+ 4 | conflicted | -1 | 99 | 4 | conflicted (deleted) | -1 | -1 | 4 | conflicted (deleted) | -1 | -1
+(1 row)
+
+-- UPDATE on view with rule
+EXPLAIN (verbose, costs off)
+UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57
+ RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on pg_temp.foo
+ Output: old.f1, old.f2, old.f3, old.f4, joinme.other, new.f1, new.f2, new.f3, new.f4, joinme.other, foo_1.f1, foo_1.f2, foo_1.f3, foo_1.f4, joinme.other, (new.f3 - old.f3)
+ Update on pg_temp.foo foo_1
+ -> Hash Join
+ Output: foo_2.f1, (foo_2.f3 + 1), joinme.ctid, foo_2.ctid, joinme_1.ctid, joinme.other, foo_1.tableoid, foo_1.ctid, foo_2.tableoid
+ Hash Cond: (foo_1.f2 = joinme.f2j)
+ -> Hash Join
+ Output: foo_1.f2, foo_1.tableoid, foo_1.ctid, joinme_1.ctid, joinme_1.f2j
+ Hash Cond: (joinme_1.f2j = foo_1.f2)
+ -> Seq Scan on pg_temp.joinme joinme_1
+ Output: joinme_1.ctid, joinme_1.f2j
+ -> Hash
+ Output: foo_1.f2, foo_1.tableoid, foo_1.ctid
+ -> Seq Scan on pg_temp.foo foo_1
+ Output: foo_1.f2, foo_1.tableoid, foo_1.ctid
+ -> Hash
+ Output: joinme.ctid, joinme.other, joinme.f2j, foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid
+ -> Hash Join
+ Output: joinme.ctid, joinme.other, joinme.f2j, foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid
+ Hash Cond: (joinme.f2j = foo_2.f2)
+ -> Seq Scan on pg_temp.joinme
+ Output: joinme.ctid, joinme.other, joinme.f2j
+ -> Hash
+ Output: foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid
+ -> Seq Scan on pg_temp.foo foo_2
+ Output: foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid
+ Filter: (foo_2.f3 = 57)
+(27 rows)
+
+UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57
+ RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3;
+ f1 | f2 | f3 | f4 | other | f1 | f2 | f3 | f4 | other | f1 | f2 | f3 | f4 | other | delta_f3
+----+------+----+----+-------+----+------+----+----+-------+----+------+----+----+-------+----------
+ 3 | zoo2 | 57 | 99 | 54321 | 3 | zoo2 | 58 | 99 | 54321 | 3 | zoo2 | 58 | 99 | 54321 | 1
+(1 row)
+
+-- UPDATE on view with INSTEAD OF trigger
+CREATE FUNCTION joinview_upd_trig_fn() RETURNS trigger
+LANGUAGE plpgsql AS
+$$
+BEGIN
+ RAISE NOTICE 'UPDATE: % -> %', old, new;
+ UPDATE foo SET f1 = new.f1, f3 = new.f3, f4 = new.f4 * 10
+ FROM joinme WHERE f2 = f2j AND f2 = old.f2
+ RETURNING new.f1, new.f4 INTO new.f1, new.f4; -- should fail
+ RETURN NEW;
+END;
+$$;
+CREATE TRIGGER joinview_upd_trig INSTEAD OF UPDATE ON joinview
+ FOR EACH ROW EXECUTE FUNCTION joinview_upd_trig_fn();
+DROP RULE joinview_u ON joinview;
+UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58
+ RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; -- should fail
+NOTICE: UPDATE: (3,zoo2,58,99,54321) -> (3,zoo2,59,7,54321)
+ERROR: column reference "new.f1" is ambiguous
+LINE 3: RETURNING new.f1, new.f4
+ ^
+DETAIL: It could refer to either a PL/pgSQL variable or a table column.
+QUERY: UPDATE foo SET f1 = new.f1, f3 = new.f3, f4 = new.f4 * 10
+ FROM joinme WHERE f2 = f2j AND f2 = old.f2
+ RETURNING new.f1, new.f4
+CONTEXT: PL/pgSQL function joinview_upd_trig_fn() line 4 at SQL statement
+CREATE OR REPLACE FUNCTION joinview_upd_trig_fn() RETURNS trigger
+LANGUAGE plpgsql AS
+$$
+BEGIN
+ RAISE NOTICE 'UPDATE: % -> %', old, new;
+ UPDATE foo SET f1 = new.f1, f3 = new.f3, f4 = new.f4 * 10
+ FROM joinme WHERE f2 = f2j AND f2 = old.f2
+ RETURNING WITH (new AS n) new.f1, n.f4 INTO new.f1, new.f4; -- now ok
+ RETURN NEW;
+END;
+$$;
+EXPLAIN (verbose, costs off)
+UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58
+ RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on pg_temp.joinview
+ Output: old.f1, old.f2, old.f3, old.f4, old.other, new.f1, new.f2, new.f3, new.f4, new.other, joinview.f1, joinview.f2, joinview.f3, joinview.f4, joinview.other, (new.f3 - old.f3)
+ -> Hash Join
+ Output: (foo.f3 + 1), '7'::bigint, ROW(foo.f1, foo.f2, foo.f3, foo.f4, joinme.other), foo.ctid, joinme.ctid, foo.tableoid
+ Hash Cond: (joinme.f2j = foo.f2)
+ -> Seq Scan on pg_temp.joinme
+ Output: joinme.other, joinme.ctid, joinme.f2j
+ -> Hash
+ Output: foo.f3, foo.f1, foo.f2, foo.f4, foo.ctid, foo.tableoid
+ -> Seq Scan on pg_temp.foo
+ Output: foo.f3, foo.f1, foo.f2, foo.f4, foo.ctid, foo.tableoid
+ Filter: (foo.f3 = 58)
+(12 rows)
+
+UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58
+ RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; -- should succeed
+NOTICE: UPDATE: (3,zoo2,58,99,54321) -> (3,zoo2,59,7,54321)
+ f1 | f2 | f3 | f4 | other | f1 | f2 | f3 | f4 | other | f1 | f2 | f3 | f4 | other | delta_f3
+----+------+----+----+-------+----+------+----+----+-------+----+------+----+----+-------+----------
+ 3 | zoo2 | 58 | 99 | 54321 | 3 | zoo2 | 59 | 70 | 54321 | 3 | zoo2 | 59 | 70 | 54321 | 1
+(1 row)
+
+-- Test wholerow & dropped column handling
+ALTER TABLE foo DROP COLUMN f3 CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to rule voo_i on view voo
+drop cascades to view joinview
+drop cascades to rule foo_del_rule on table foo
+UPDATE foo SET f4 = f4 + 1 RETURNING old.f3; -- should fail
+ERROR: column old.f3 does not exist
+LINE 1: UPDATE foo SET f4 = f4 + 1 RETURNING old.f3;
+ ^
+UPDATE foo SET f4 = f4 + 1 RETURNING old, new;
+ old | new
+-------------------------------+------------------------------
+ (1,xxx,20) | (1,xxx,21)
+ (2,more,141) | (2,more,142)
+ (4,"conflicted (deleted)",-1) | (4,"conflicted (deleted)",0)
+ (3,zoo2,70) | (3,zoo2,71)
+(4 rows)
+
+-- INSERT/DELETE on zero column table
+CREATE TABLE zerocol();
+INSERT INTO zerocol SELECT
+ RETURNING old.tableoid::regclass, old.ctid,
+ new.tableoid::regclass, new.ctid, ctid, *;
+ tableoid | ctid | tableoid | ctid | ctid
+----------+------+----------+-------+-------
+ zerocol | | zerocol | (0,1) | (0,1)
+(1 row)
+
+DELETE FROM zerocol
+ RETURNING old.tableoid::regclass, old.ctid,
+ new.tableoid::regclass, new.ctid, ctid, *;
+ tableoid | ctid | tableoid | ctid | ctid
+----------+-------+----------+------+-------
+ zerocol | (0,1) | zerocol | | (0,1)
+(1 row)
+
+DROP TABLE zerocol;
+-- Test cross-partition updates and attribute mapping
+CREATE TABLE foo_parted (a int, b float8, c text) PARTITION BY LIST (a);
+CREATE TABLE foo_part_s1 PARTITION OF foo_parted FOR VALUES IN (1);
+CREATE TABLE foo_part_s2 PARTITION OF foo_parted FOR VALUES IN (2);
+CREATE TABLE foo_part_d1 (c text, a int, b float8);
+ALTER TABLE foo_parted ATTACH PARTITION foo_part_d1 FOR VALUES IN (3);
+CREATE TABLE foo_part_d2 (b float8, c text, a int);
+ALTER TABLE foo_parted ATTACH PARTITION foo_part_d2 FOR VALUES IN (4);
+INSERT INTO foo_parted
+ VALUES (1, 17.1, 'P1'), (2, 17.2, 'P2'), (3, 17.3, 'P3'), (4, 17.4, 'P4')
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+ tableoid | ctid | a | b | c | tableoid | ctid | a | b | c | a | b | c
+-------------+------+---+---+---+-------------+-------+---+------+----+---+------+----
+ foo_part_s1 | | | | | foo_part_s1 | (0,1) | 1 | 17.1 | P1 | 1 | 17.1 | P1
+ foo_part_s2 | | | | | foo_part_s2 | (0,1) | 2 | 17.2 | P2 | 2 | 17.2 | P2
+ foo_part_d1 | | | | | foo_part_d1 | (0,1) | 3 | 17.3 | P3 | 3 | 17.3 | P3
+ foo_part_d2 | | | | | foo_part_d2 | (0,1) | 4 | 17.4 | P4 | 4 | 17.4 | P4
+(4 rows)
+
+UPDATE foo_parted SET a = 2, b = b + 1, c = c || '->P2' WHERE a = 1
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+ tableoid | ctid | a | b | c | tableoid | ctid | a | b | c | a | b | c
+-------------+-------+---+------+----+-------------+-------+---+------+--------+---+------+--------
+ foo_part_s1 | (0,1) | 1 | 17.1 | P1 | foo_part_s2 | (0,2) | 2 | 18.1 | P1->P2 | 2 | 18.1 | P1->P2
+(1 row)
+
+UPDATE foo_parted SET a = 1, b = b + 1, c = c || '->P1' WHERE a = 3
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+ tableoid | ctid | a | b | c | tableoid | ctid | a | b | c | a | b | c
+-------------+-------+---+------+----+-------------+-------+---+------+--------+---+------+--------
+ foo_part_d1 | (0,1) | 3 | 17.3 | P3 | foo_part_s1 | (0,2) | 1 | 18.3 | P3->P1 | 1 | 18.3 | P3->P1
+(1 row)
+
+UPDATE foo_parted SET a = 3, b = b + 1, c = c || '->P3' WHERE a = 1
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+ tableoid | ctid | a | b | c | tableoid | ctid | a | b | c | a | b | c
+-------------+-------+---+------+--------+-------------+-------+---+------+------------+---+------+------------
+ foo_part_s1 | (0,2) | 1 | 18.3 | P3->P1 | foo_part_d1 | (0,2) | 3 | 19.3 | P3->P1->P3 | 3 | 19.3 | P3->P1->P3
+(1 row)
+
+UPDATE foo_parted SET a = 4, b = b + 1, c = c || '->P4' WHERE a = 3
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+ tableoid | ctid | a | b | c | tableoid | ctid | a | b | c | a | b | c
+-------------+-------+---+------+------------+-------------+-------+---+------+----------------+---+------+----------------
+ foo_part_d1 | (0,2) | 3 | 19.3 | P3->P1->P3 | foo_part_d2 | (0,2) | 4 | 20.3 | P3->P1->P3->P4 | 4 | 20.3 | P3->P1->P3->P4
+(1 row)
+
+DELETE FROM foo_parted
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+ tableoid | ctid | a | b | c | tableoid | ctid | a | b | c | a | b | c
+-------------+-------+---+------+----------------+-------------+------+---+---+---+---+------+----------------
+ foo_part_s2 | (0,1) | 2 | 17.2 | P2 | foo_part_s2 | | | | | 2 | 17.2 | P2
+ foo_part_s2 | (0,2) | 2 | 18.1 | P1->P2 | foo_part_s2 | | | | | 2 | 18.1 | P1->P2
+ foo_part_d2 | (0,1) | 4 | 17.4 | P4 | foo_part_d2 | | | | | 4 | 17.4 | P4
+ foo_part_d2 | (0,2) | 4 | 20.3 | P3->P1->P3->P4 | foo_part_d2 | | | | | 4 | 20.3 | P3->P1->P3->P4
+(4 rows)
+
+DROP TABLE foo_parted;
+-- Test deparsing
+CREATE FUNCTION foo_update()
+ RETURNS void
+ LANGUAGE sql
+BEGIN ATOMIC
+ UPDATE foo SET f1 = f1 + 1
+ RETURNING WITH (OLD AS o)
+ o.*, new.*, o, new, o.f1 = new.f1, o = new,
+ (SELECT o.f2 = new.f2),
+ (SELECT count(*) FROM foo WHERE foo.f1 = o.f4),
+ (SELECT count(*) FROM foo WHERE foo.f4 = new.f4),
+ (SELECT count(*) FROM foo WHERE foo = o),
+ (SELECT count(*) FROM foo WHERE foo = new);
+END;
+\sf foo_update
+CREATE OR REPLACE FUNCTION public.foo_update()
+ RETURNS void
+ LANGUAGE sql
+BEGIN ATOMIC
+ UPDATE foo SET f1 = (foo.f1 + 1)
+ RETURNING WITH (OLD AS o) o.f1,
+ o.f2,
+ o.f4,
+ new.f1,
+ new.f2,
+ new.f4,
+ o.*::foo AS o,
+ new.*::foo AS new,
+ (o.f1 = new.f1),
+ (o.* = new.*),
+ ( SELECT (o.f2 = new.f2)),
+ ( SELECT count(*) AS count
+ FROM foo foo_1
+ WHERE (foo_1.f1 = o.f4)) AS count,
+ ( SELECT count(*) AS count
+ FROM foo foo_1
+ WHERE (foo_1.f4 = new.f4)) AS count,
+ ( SELECT count(*) AS count
+ FROM foo foo_1
+ WHERE (foo_1.* = o.*)) AS count,
+ ( SELECT count(*) AS count
+ FROM foo foo_1
+ WHERE (foo_1.* = new.*)) AS count;
+END
+DROP FUNCTION foo_update;
diff --git a/src/test/regress/sql/returning.sql b/src/test/regress/sql/returning.sql
new file mode 100644
index a460f82..29841a9
--- a/src/test/regress/sql/returning.sql
+++ b/src/test/regress/sql/returning.sql
@@ -160,3 +160,205 @@ INSERT INTO foo AS bar DEFAULT VALUES RE
INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*; -- fails, wrong name
INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.*; -- ok
INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.f3; -- ok
+
+--
+-- Test RETURNING OLD/NEW.
+--
+-- Start with new data, to ensure predictable TIDs.
+--
+TRUNCATE foo;
+INSERT INTO foo VALUES (1, 'xxx', 10, 20), (2, 'more', 42, 141), (3, 'zoo2', 57, 99);
+
+-- Error cases
+INSERT INTO foo DEFAULT VALUES RETURNING WITH (nonsuch AS something) *;
+INSERT INTO foo DEFAULT VALUES RETURNING WITH (new AS foo) *;
+INSERT INTO foo DEFAULT VALUES RETURNING WITH (old AS o, new AS n, old AS o) *;
+
+-- INSERT has NEW, but not OLD
+EXPLAIN (verbose, costs off)
+INSERT INTO foo VALUES (4)
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+INSERT INTO foo VALUES (4)
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+
+-- INSERT ... ON CONFLICT ... UPDATE has OLD and NEW
+CREATE UNIQUE INDEX foo_f1_idx ON foo (f1);
+EXPLAIN (verbose, costs off)
+INSERT INTO foo VALUES (4, 'conflict'), (5, 'ok')
+ ON CONFLICT (f1) DO UPDATE SET f2 = excluded.f2||'ed', f3 = -1
+ RETURNING WITH (OLD AS o, NEW AS n)
+ o.tableoid::regclass, o.ctid, o.*,
+ n.tableoid::regclass, n.ctid, n.*, *;
+INSERT INTO foo VALUES (4, 'conflict'), (5, 'ok')
+ ON CONFLICT (f1) DO UPDATE SET f2 = excluded.f2||'ed', f3 = -1
+ RETURNING WITH (OLD AS o, NEW AS n)
+ o.tableoid::regclass, o.ctid, o.*,
+ n.tableoid::regclass, n.ctid, n.*, *;
+
+-- UPDATE has OLD and NEW
+EXPLAIN (verbose, costs off)
+UPDATE foo SET f4 = 100 WHERE f1 = 5
+ RETURNING old.tableoid::regclass, old.ctid, old.*, old,
+ new.tableoid::regclass, new.ctid, new.*, new,
+ old.f4::text||'->'||new.f4::text AS change;
+UPDATE foo SET f4 = 100 WHERE f1 = 5
+ RETURNING old.tableoid::regclass, old.ctid, old.*, old,
+ new.tableoid::regclass, new.ctid, new.*, new,
+ old.f4::text||'->'||new.f4::text AS change;
+
+-- DELETE has OLD, but not NEW
+EXPLAIN (verbose, costs off)
+DELETE FROM foo WHERE f1 = 5
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+DELETE FROM foo WHERE f1 = 5
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+
+-- RETURNING OLD and NEW from subquery
+EXPLAIN (verbose, costs off)
+INSERT INTO foo VALUES (5, 'subquery test')
+ RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max,
+ (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
+INSERT INTO foo VALUES (5, 'subquery test')
+ RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max,
+ (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
+
+EXPLAIN (verbose, costs off)
+UPDATE foo SET f4 = 100 WHERE f1 = 5
+ RETURNING (SELECT old.f4 = new.f4),
+ (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max,
+ (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
+UPDATE foo SET f4 = 100 WHERE f1 = 5
+ RETURNING (SELECT old.f4 = new.f4),
+ (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max,
+ (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
+
+EXPLAIN (verbose, costs off)
+DELETE FROM foo WHERE f1 = 5
+ RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max,
+ (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
+DELETE FROM foo WHERE f1 = 5
+ RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max,
+ (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
+
+-- DELETE turned into UPDATE by a rule has OLD and NEW
+CREATE RULE foo_del_rule AS ON DELETE TO foo DO INSTEAD
+ UPDATE foo SET f2 = f2||' (deleted)', f3 = -1, f4 = -1 WHERE f1 = OLD.f1
+ RETURNING *;
+EXPLAIN (verbose, costs off)
+DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *;
+DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *;
+
+-- UPDATE on view with rule
+EXPLAIN (verbose, costs off)
+UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57
+ RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3;
+UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57
+ RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3;
+
+-- UPDATE on view with INSTEAD OF trigger
+CREATE FUNCTION joinview_upd_trig_fn() RETURNS trigger
+LANGUAGE plpgsql AS
+$$
+BEGIN
+ RAISE NOTICE 'UPDATE: % -> %', old, new;
+ UPDATE foo SET f1 = new.f1, f3 = new.f3, f4 = new.f4 * 10
+ FROM joinme WHERE f2 = f2j AND f2 = old.f2
+ RETURNING new.f1, new.f4 INTO new.f1, new.f4; -- should fail
+ RETURN NEW;
+END;
+$$;
+CREATE TRIGGER joinview_upd_trig INSTEAD OF UPDATE ON joinview
+ FOR EACH ROW EXECUTE FUNCTION joinview_upd_trig_fn();
+DROP RULE joinview_u ON joinview;
+UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58
+ RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; -- should fail
+
+CREATE OR REPLACE FUNCTION joinview_upd_trig_fn() RETURNS trigger
+LANGUAGE plpgsql AS
+$$
+BEGIN
+ RAISE NOTICE 'UPDATE: % -> %', old, new;
+ UPDATE foo SET f1 = new.f1, f3 = new.f3, f4 = new.f4 * 10
+ FROM joinme WHERE f2 = f2j AND f2 = old.f2
+ RETURNING WITH (new AS n) new.f1, n.f4 INTO new.f1, new.f4; -- now ok
+ RETURN NEW;
+END;
+$$;
+EXPLAIN (verbose, costs off)
+UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58
+ RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3;
+UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58
+ RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; -- should succeed
+
+-- Test wholerow & dropped column handling
+ALTER TABLE foo DROP COLUMN f3 CASCADE;
+UPDATE foo SET f4 = f4 + 1 RETURNING old.f3; -- should fail
+UPDATE foo SET f4 = f4 + 1 RETURNING old, new;
+
+-- INSERT/DELETE on zero column table
+CREATE TABLE zerocol();
+INSERT INTO zerocol SELECT
+ RETURNING old.tableoid::regclass, old.ctid,
+ new.tableoid::regclass, new.ctid, ctid, *;
+DELETE FROM zerocol
+ RETURNING old.tableoid::regclass, old.ctid,
+ new.tableoid::regclass, new.ctid, ctid, *;
+DROP TABLE zerocol;
+
+-- Test cross-partition updates and attribute mapping
+CREATE TABLE foo_parted (a int, b float8, c text) PARTITION BY LIST (a);
+CREATE TABLE foo_part_s1 PARTITION OF foo_parted FOR VALUES IN (1);
+CREATE TABLE foo_part_s2 PARTITION OF foo_parted FOR VALUES IN (2);
+CREATE TABLE foo_part_d1 (c text, a int, b float8);
+ALTER TABLE foo_parted ATTACH PARTITION foo_part_d1 FOR VALUES IN (3);
+CREATE TABLE foo_part_d2 (b float8, c text, a int);
+ALTER TABLE foo_parted ATTACH PARTITION foo_part_d2 FOR VALUES IN (4);
+
+INSERT INTO foo_parted
+ VALUES (1, 17.1, 'P1'), (2, 17.2, 'P2'), (3, 17.3, 'P3'), (4, 17.4, 'P4')
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+
+UPDATE foo_parted SET a = 2, b = b + 1, c = c || '->P2' WHERE a = 1
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+
+UPDATE foo_parted SET a = 1, b = b + 1, c = c || '->P1' WHERE a = 3
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+
+UPDATE foo_parted SET a = 3, b = b + 1, c = c || '->P3' WHERE a = 1
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+
+UPDATE foo_parted SET a = 4, b = b + 1, c = c || '->P4' WHERE a = 3
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+
+DELETE FROM foo_parted
+ RETURNING old.tableoid::regclass, old.ctid, old.*,
+ new.tableoid::regclass, new.ctid, new.*, *;
+
+DROP TABLE foo_parted;
+
+-- Test deparsing
+CREATE FUNCTION foo_update()
+ RETURNS void
+ LANGUAGE sql
+BEGIN ATOMIC
+ UPDATE foo SET f1 = f1 + 1
+ RETURNING WITH (OLD AS o)
+ o.*, new.*, o, new, o.f1 = new.f1, o = new,
+ (SELECT o.f2 = new.f2),
+ (SELECT count(*) FROM foo WHERE foo.f1 = o.f4),
+ (SELECT count(*) FROM foo WHERE foo.f4 = new.f4),
+ (SELECT count(*) FROM foo WHERE foo = o),
+ (SELECT count(*) FROM foo WHERE foo = new);
+END;
+
+\sf foo_update
+DROP FUNCTION foo_update;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
new file mode 100644
index aa7a25b..8f5fde1
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2364,6 +2364,7 @@ ReorderBufferUpdateProgressTxnCB
ReorderTuple
RepOriginId
ReparameterizeForeignPathByChild_function
+ReplaceReturningVarsFromTargetList_context
ReplaceVarsFromTargetList_context
ReplaceVarsNoMatchOption
ReplicaIdentityStmt
@@ -2393,6 +2394,8 @@ RestrictInfo
Result
ResultRelInfo
ResultState
+ReturningClause
+ReturningOption
ReturnSetInfo
ReturnStmt
RevmapContents
@@ -2537,6 +2540,7 @@ SetOperationStmt
SetQuantifier
SetToDefault
SetupWorkerPtrType
+SetVarReturningType_context
ShDependObjectInfo
SharedAggInfo
SharedBitmapState
@@ -2991,6 +2995,7 @@ VariableSpace
VariableStatData
VariableSubstituteHook
Variables
+VarReturningType
Vector32
Vector8
VersionedQuery