path: root/src/test/regress/expected/returning.out

--
-- Test INSERT/UPDATE/DELETE RETURNING
--
-- Simple cases
CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42);
INSERT INTO foo (f2,f3)
  VALUES ('test', DEFAULT), ('More', 11), (upper('more'), 7+9)
  RETURNING *, f1+f3 AS sum;
 f1 |  f2  | f3 | sum 
----+------+----+-----
  1 | test | 42 |  43
  2 | More | 11 |  13
  3 | MORE | 16 |  19
(3 rows)

SELECT * FROM foo;
 f1 |  f2  | f3 
----+------+----
  1 | test | 42
  2 | More | 11
  3 | MORE | 16
(3 rows)

UPDATE foo SET f2 = lower(f2), f3 = DEFAULT RETURNING foo.*, f1+f3 AS sum13;
 f1 |  f2  | f3 | sum13 
----+------+----+-------
  1 | test | 42 |    43
  2 | more | 42 |    44
  3 | more | 42 |    45
(3 rows)

SELECT * FROM foo;
 f1 |  f2  | f3 
----+------+----
  1 | test | 42
  2 | more | 42
  3 | more | 42
(3 rows)

DELETE FROM foo WHERE f1 > 2 RETURNING f3, f2, f1, least(f1,f3);
 f3 |  f2  | f1 | least 
----+------+----+-------
 42 | more |  3 |     3
(1 row)

SELECT * FROM foo;
 f1 |  f2  | f3 
----+------+----
  1 | test | 42
  2 | more | 42
(2 rows)

-- Subplans and initplans in the RETURNING list
INSERT INTO foo SELECT f1+10, f2, f3+99 FROM foo
  RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan,
    EXISTS(SELECT * FROM int4_tbl) AS initplan;
 f1 |  f2  | f3  | subplan | initplan 
----+------+-----+---------+----------
 11 | test | 141 | t       | t
 12 | more | 141 | f       | t
(2 rows)

UPDATE foo SET f3 = f3 * 2
  WHERE f1 > 10
  RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan,
    EXISTS(SELECT * FROM int4_tbl) AS initplan;
 f1 |  f2  | f3  | subplan | initplan 
----+------+-----+---------+----------
 11 | test | 282 | t       | t
 12 | more | 282 | f       | t
(2 rows)

DELETE FROM foo
  WHERE f1 > 10
  RETURNING *, f1+112 IN (SELECT q1 FROM int8_tbl) AS subplan,
    EXISTS(SELECT * FROM int4_tbl) AS initplan;
 f1 |  f2  | f3  | subplan | initplan 
----+------+-----+---------+----------
 11 | test | 282 | t       | t
 12 | more | 282 | f       | t
(2 rows)

-- Joins
UPDATE foo SET f3 = f3*2
  FROM int4_tbl i
  WHERE foo.f1 + 123455 = i.f1
  RETURNING foo.*, i.f1 as "i.f1";
 f1 |  f2  | f3 |  i.f1  
----+------+----+--------
  1 | test | 84 | 123456
(1 row)

SELECT * FROM foo;
 f1 |  f2  | f3 
----+------+----
  2 | more | 42
  1 | test | 84
(2 rows)

DELETE FROM foo
  USING int4_tbl i
  WHERE foo.f1 + 123455 = i.f1
  RETURNING foo.*, i.f1 as "i.f1";
 f1 |  f2  | f3 |  i.f1  
----+------+----+--------
  1 | test | 84 | 123456
(1 row)

SELECT * FROM foo;
 f1 |  f2  | f3 
----+------+----
  2 | more | 42
(1 row)

-- Check inheritance cases
CREATE TEMP TABLE foochild (fc int) INHERITS (foo);
INSERT INTO foochild VALUES(123,'child',999,-123);
ALTER TABLE foo ADD COLUMN f4 int8 DEFAULT 99;
SELECT * FROM foo;
 f1  |  f2   | f3  | f4 
-----+-------+-----+----
   2 | more  |  42 | 99
 123 | child | 999 | 99
(2 rows)

SELECT * FROM foochild;
 f1  |  f2   | f3  |  fc  | f4 
-----+-------+-----+------+----
 123 | child | 999 | -123 | 99
(1 row)

UPDATE foo SET f4 = f4 + f3 WHERE f4 = 99 RETURNING *;
 f1  |  f2   | f3  |  f4  
-----+-------+-----+------
   2 | more  |  42 |  141
 123 | child | 999 | 1098
(2 rows)

SELECT * FROM foo;
 f1  |  f2   | f3  |  f4  
-----+-------+-----+------
   2 | more  |  42 |  141
 123 | child | 999 | 1098
(2 rows)

SELECT * FROM foochild;
 f1  |  f2   | f3  |  fc  |  f4  
-----+-------+-----+------+------
 123 | child | 999 | -123 | 1098
(1 row)

UPDATE foo SET f3 = f3*2
  FROM int8_tbl i
  WHERE foo.f1 = i.q2
  RETURNING *;
 f1  |  f2   |  f3  |  f4  |        q1        | q2  
-----+-------+------+------+------------------+-----
 123 | child | 1998 | 1098 | 4567890123456789 | 123
(1 row)

SELECT * FROM foo;
 f1  |  f2   |  f3  |  f4  
-----+-------+------+------
   2 | more  |   42 |  141
 123 | child | 1998 | 1098
(2 rows)

SELECT * FROM foochild;
 f1  |  f2   |  f3  |  fc  |  f4  
-----+-------+------+------+------
 123 | child | 1998 | -123 | 1098
(1 row)

DELETE FROM foo
  USING int8_tbl i
  WHERE foo.f1 = i.q2
  RETURNING *;
 f1  |  f2   |  f3  |  f4  |        q1        | q2  
-----+-------+------+------+------------------+-----
 123 | child | 1998 | 1098 | 4567890123456789 | 123
(1 row)

SELECT * FROM foo;
 f1 |  f2  | f3 | f4  
----+------+----+-----
  2 | more | 42 | 141
(1 row)

SELECT * FROM foochild;
 f1 | f2 | f3 | fc | f4 
----+----+----+----+----
(0 rows)

DROP TABLE foochild;
-- Rules and views
CREATE TEMP VIEW voo AS SELECT f1, f2 FROM foo;
CREATE RULE voo_i AS ON INSERT TO voo DO INSTEAD
  INSERT INTO foo VALUES(new.*, 57);
INSERT INTO voo VALUES(11,'zit');
-- fails:
INSERT INTO voo VALUES(12,'zoo') RETURNING *, f1*2;
ERROR:  cannot perform INSERT RETURNING on relation "voo"
HINT:  You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.
-- fails, incompatible list:
CREATE OR REPLACE RULE voo_i AS ON INSERT TO voo DO INSTEAD
  INSERT INTO foo VALUES(new.*, 57) RETURNING *;
ERROR:  RETURNING list has too many entries
CREATE OR REPLACE RULE voo_i AS ON INSERT TO voo DO INSTEAD
  INSERT INTO foo VALUES(new.*, 57) RETURNING f1, f2;
-- should still work
INSERT INTO voo VALUES(13,'zit2');
-- works now
INSERT INTO voo VALUES(14,'zoo2') RETURNING *;
 f1 |  f2  
----+------
 14 | zoo2
(1 row)

SELECT * FROM foo;
 f1 |  f2  | f3 | f4  
----+------+----+-----
  2 | more | 42 | 141
 11 | zit  | 57 |  99
 13 | zit2 | 57 |  99
 14 | zoo2 | 57 |  99
(4 rows)

SELECT * FROM voo;
 f1 |  f2  
----+------
  2 | more
 11 | zit
 13 | zit2
 14 | zoo2
(4 rows)

CREATE OR REPLACE RULE voo_u AS ON UPDATE TO voo DO INSTEAD
  UPDATE foo SET f1 = new.f1, f2 = new.f2 WHERE f1 = old.f1
  RETURNING f1, f2;
update voo set f1 = f1 + 1 where f2 = 'zoo2';
update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2;
 f1 |  f2  | ?column? 
----+------+----------
 16 | zoo2 |       32
(1 row)

SELECT * FROM foo;
 f1 |  f2  | f3 | f4  
----+------+----+-----
  2 | more | 42 | 141
 11 | zit  | 57 |  99
 13 | zit2 | 57 |  99
 16 | zoo2 | 57 |  99
(4 rows)

SELECT * FROM voo;
 f1 |  f2  
----+------
  2 | more
 11 | zit
 13 | zit2
 16 | zoo2
(4 rows)

CREATE OR REPLACE RULE voo_d AS ON DELETE TO voo DO INSTEAD
  DELETE FROM foo WHERE f1 = old.f1
  RETURNING f1, f2;
DELETE FROM foo WHERE f1 = 13;
DELETE FROM foo WHERE f2 = 'zit' RETURNING *;
 f1 | f2  | f3 | f4 
----+-----+----+----
 11 | zit | 57 | 99
(1 row)

SELECT * FROM foo;
 f1 |  f2  | f3 | f4  
----+------+----+-----
  2 | more | 42 | 141
 16 | zoo2 | 57 |  99
(2 rows)

SELECT * FROM voo;
 f1 |  f2  
----+------
  2 | more
 16 | zoo2
(2 rows)

-- Check use of a whole-row variable for an un-flattenable view
CREATE TEMP VIEW foo_v AS SELECT * FROM foo OFFSET 0;
UPDATE foo SET f2 = foo_v.f2 FROM foo_v WHERE foo_v.f1 = foo.f1
  RETURNING foo_v;
      foo_v      
-----------------
 (2,more,42,141)
 (16,zoo2,57,99)
(2 rows)

SELECT * FROM foo;
 f1 |  f2  | f3 | f4  
----+------+----+-----
  2 | more | 42 | 141
 16 | zoo2 | 57 |  99
(2 rows)

-- Check use of a whole-row variable for an inlined set-returning function
CREATE FUNCTION foo_f() RETURNS SETOF foo AS
  $$ SELECT * FROM foo OFFSET 0 $$ LANGUAGE sql STABLE;
UPDATE foo SET f2 = foo_f.f2 FROM foo_f() WHERE foo_f.f1 = foo.f1
  RETURNING foo_f;
      foo_f      
-----------------
 (2,more,42,141)
 (16,zoo2,57,99)
(2 rows)

SELECT * FROM foo;
 f1 |  f2  | f3 | f4  
----+------+----+-----
  2 | more | 42 | 141
 16 | zoo2 | 57 |  99
(2 rows)

DROP FUNCTION foo_f();
-- As above, but SRF is defined to return a composite type
CREATE TYPE foo_t AS (f1 int, f2 text, f3 int, f4 int8);
CREATE FUNCTION foo_f() RETURNS SETOF foo_t AS
  $$ SELECT * FROM foo OFFSET 0 $$ LANGUAGE sql STABLE;
UPDATE foo SET f2 = foo_f.f2 FROM foo_f() WHERE foo_f.f1 = foo.f1
  RETURNING foo_f;
      foo_f      
-----------------
 (2,more,42,141)
 (16,zoo2,57,99)
(2 rows)

SELECT * FROM foo;
 f1 |  f2  | f3 | f4  
----+------+----+-----
  2 | more | 42 | 141
 16 | zoo2 | 57 |  99
(2 rows)

DROP FUNCTION foo_f();
DROP TYPE foo_t;
-- Try a join case
CREATE TEMP TABLE joinme (f2j text, other int);
INSERT INTO joinme VALUES('more', 12345);
INSERT INTO joinme VALUES('zoo2', 54321);
INSERT INTO joinme VALUES('other', 0);
CREATE TEMP VIEW joinview AS
  SELECT foo.*, other FROM foo JOIN joinme ON (f2 = f2j);
SELECT * FROM joinview;
 f1 |  f2  | f3 | f4  | other 
----+------+----+-----+-------
  2 | more | 42 | 141 | 12345
 16 | zoo2 | 57 |  99 | 54321
(2 rows)

CREATE RULE joinview_u AS ON UPDATE TO joinview DO INSTEAD
  UPDATE foo SET f1 = new.f1, f3 = new.f3
    FROM joinme WHERE f2 = f2j AND f2 = old.f2
    RETURNING foo.*, other;
UPDATE joinview SET f1 = f1 + 1 WHERE f3 = 57 RETURNING *, other + 1;
 f1 |  f2  | f3 | f4 | other | ?column? 
----+------+----+----+-------+----------
 17 | zoo2 | 57 | 99 | 54321 |    54322
(1 row)

SELECT * FROM joinview;
 f1 |  f2  | f3 | f4  | other 
----+------+----+-----+-------
  2 | more | 42 | 141 | 12345
 17 | zoo2 | 57 |  99 | 54321
(2 rows)

SELECT * FROM foo;
 f1 |  f2  | f3 | f4  
----+------+----+-----
  2 | more | 42 | 141
 17 | zoo2 | 57 |  99
(2 rows)

SELECT * FROM voo;
 f1 |  f2  
----+------
  2 | more
 17 | zoo2
(2 rows)

-- Check aliased target relation
INSERT INTO foo AS bar DEFAULT VALUES RETURNING *; -- ok
 f1 | f2 | f3 | f4 
----+----+----+----
  4 |    | 42 | 99
(1 row)

INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*; -- fails, wrong name
ERROR:  invalid reference to FROM-clause entry for table "foo"
LINE 1: INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*;
                                                        ^
HINT:  Perhaps you meant to reference the table alias "bar".
INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.*; -- ok
 f1 | f2 | f3 | f4 
----+----+----+----
  5 |    | 42 | 99
(1 row)

INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.f3; -- ok
 f3 
----
 42
(1 row)

--
-- 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 INTO foo DEFAULT VALUES RETURNING WITH (old AS o, new AS n, new AS n) *;
ERROR:  NEW cannot be specified multiple times
LINE 1: ...EFAULT VALUES RETURNING WITH (old AS o, new AS n, new AS n) ...
                                                             ^
INSERT INTO foo DEFAULT VALUES RETURNING WITH (old AS x, new AS x) *;
ERROR:  table name "x" specified more than once
LINE 1: ...INTO foo DEFAULT VALUES RETURNING WITH (old AS x, new AS x) ...
                                                             ^
-- 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      | (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      | (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 |          |      |    |    |    |    |  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 4 other objects
DETAIL:  drop cascades to rule voo_i on view voo
drop cascades to view foo_v
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.*, new.*, *;
ERROR:  RETURNING must have at least one column
LINE 1: INSERT INTO zerocol SELECT RETURNING old.*, new.*, *;
                                             ^
INSERT INTO zerocol SELECT
  RETURNING old.tableoid::regclass, old.ctid,
            new.tableoid::regclass, new.ctid, ctid, *;
 tableoid | ctid | tableoid | ctid  | ctid  
----------+------+----------+-------+-------
          |      | 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) |          |      | (0,1)
(1 row)

DROP TABLE zerocol;
-- Test schema-qualified table name in RETURNING list
CREATE TABLE public.tt(a int, b int);
INSERT INTO public.tt VALUES (1, 10);
UPDATE public.tt SET b = b * 2 RETURNING a, b, old.b, new.b, tt.b, public.tt.b;
 a | b  | b  | b  | b  | b  
---+----+----+----+----+----
 1 | 20 | 10 | 20 | 20 | 20
(1 row)

DROP TABLE public.tt;
-- 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 | (0,1) | 1 | 17.1 | P1 | 1 | 17.1 | P1
          |      |   |   |   | foo_part_s2 | (0,1) | 2 | 17.2 | P2 | 2 | 17.2 | P2
          |      |   |   |   | foo_part_d1 | (0,1) | 3 | 17.3 | P3 | 3 | 17.3 | P3
          |      |   |   |   | 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)

-- cross-partition update that uses ReturningExpr nodes, without returning
-- old/new table values
CREATE VIEW foo_parted_v AS SELECT *, 'xxx' AS dummy FROM foo_parted;
UPDATE foo_parted_v SET a = 1, c = c || '->P1' WHERE a = 2 AND c = 'P2'
  RETURNING 'P2:'||old.dummy, 'P1:'||new.dummy;
 ?column? | ?column? 
----------+----------
 P2:xxx   | P1:xxx
(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_s1 | (0,3) | 1 | 17.2 | P2->P1         |          |      |   |   |   | 1 | 17.2 | P2->P1
 foo_part_s2 | (0,2) | 2 | 18.1 | P1->P2         |          |      |   |   |   | 2 | 18.1 | P1->P2
 foo_part_d2 | (0,1) | 4 | 17.4 | P4             |          |      |   |   |   | 4 | 17.4 | P4
 foo_part_d2 | (0,2) | 4 | 20.3 | P3->P1->P3->P4 |          |      |   |   |   | 4 | 20.3 | P3->P1->P3->P4
(4 rows)

DROP TABLE foo_parted CASCADE;
NOTICE:  drop cascades to view foo_parted_v
-- Test deparsing
CREATE FUNCTION foo_update()
  RETURNS void
  LANGUAGE sql
BEGIN ATOMIC
  WITH u1 AS (
    UPDATE foo SET f1 = f1 + 1 RETURNING old.*, new.*
  ), u2 AS (
    UPDATE foo SET f1 = f1 + 1 RETURNING WITH (OLD AS "old foo") "old foo".*, new.*
  ), u3 AS (
    UPDATE foo SET f1 = f1 + 1 RETURNING WITH (NEW AS "new foo") old.*, "new foo".*
  )
  UPDATE foo SET f1 = f1 + 1
    RETURNING WITH (OLD AS o, NEW AS n)
              o.*, n.*, o, n, o.f1 = n.f1, o = n,
              (SELECT o.f2 = n.f2),
              (SELECT count(*) FROM foo WHERE foo.f1 = o.f4),
              (SELECT count(*) FROM foo WHERE foo.f4 = n.f4),
              (SELECT count(*) FROM foo WHERE foo = o),
              (SELECT count(*) FROM foo WHERE foo = n);
END;
\sf foo_update
CREATE OR REPLACE FUNCTION public.foo_update()
 RETURNS void
 LANGUAGE sql
BEGIN ATOMIC
 WITH u1 AS (
          UPDATE foo foo_1 SET f1 = (foo_1.f1 + 1)
           RETURNING old.f1,
             old.f2,
             old.f4,
             new.f1,
             new.f2,
             new.f4
         ), u2 AS (
          UPDATE foo foo_1 SET f1 = (foo_1.f1 + 1)
           RETURNING WITH (OLD AS "old foo") "old foo".f1,
             "old foo".f2,
             "old foo".f4,
             new.f1,
             new.f2,
             new.f4
         ), u3 AS (
          UPDATE foo foo_1 SET f1 = (foo_1.f1 + 1)
           RETURNING WITH (NEW AS "new foo") old.f1,
             old.f2,
             old.f4,
             "new foo".f1,
             "new foo".f2,
             "new foo".f4
         )
  UPDATE foo SET f1 = (foo.f1 + 1)
   RETURNING WITH (OLD AS o, NEW AS n) o.f1,
     o.f2,
     o.f4,
     n.f1,
     n.f2,
     n.f4,
     o.*::foo AS o,
     n.*::foo AS n,
     (o.f1 = n.f1),
     (o.* = n.*),
     ( SELECT (o.f2 = n.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 = n.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.* = n.*)) AS count;
END
DROP FUNCTION foo_update;