pgsql: Add OLD/NEW support to RETURNING in DML queries.

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
To: pgsql-committers(at)lists(dot)postgresql(dot)org
Subject: pgsql: Add OLD/NEW support to RETURNING in DML queries.
Date: 2025-01-16 14:59:28
Message-ID: E1tYRLD-001vzY-Qi@gemulon.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers

Add OLD/NEW support to RETURNING in DML queries.

This allows the RETURNING list of INSERT/UPDATE/DELETE/MERGE queries
to explicitly return old and new values by using the special aliases
"old" and "new", which are automatically added to the query (if not
already defined) while parsing its RETURNING list, allowing things
like:

RETURNING old.colname, new.colname, ...

RETURNING old.*, new.*

Additionally, a new syntax is supported, allowing the names "old" and
"new" to be changed to user-supplied alias names, e.g.:

RETURNING WITH (OLD AS o, NEW AS n) o.colname, n.colname, ...

This is useful when the names "old" and "new" are already defined,
such as inside trigger functions, allowing backwards compatibility to
be maintained -- the interpretation of any existing queries that
happen to already refer to relations called "old" or "new", or use
those as aliases for other relations, is not changed.

For an INSERT, old values will generally be NULL, and for a DELETE,
new values will generally be NULL, but that may change for an INSERT
with an ON CONFLICT ... DO UPDATE clause, or if a query rewrite rule
changes the command type. Therefore, we put no restrictions on the use
of old and new in any DML queries.

Dean Rasheed, reviewed by Jian He and Jeff Davis.

Discussion: https://postgr.es/m/CAEZATCWx0J0-v=Qjc6gXzR=KtsdvAE7Ow=D=mu50AgOe+pvisQ@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/80feb727c869cc0b2e12bd1543bafa449be9c8e2

Modified Files
--------------
contrib/postgres_fdw/expected/postgres_fdw.out | 124 +++++-
contrib/postgres_fdw/sql/postgres_fdw.sql | 25 +-
doc/src/sgml/dml.sgml | 41 +-
doc/src/sgml/ref/delete.sgml | 40 +-
doc/src/sgml/ref/insert.sgml | 54 ++-
doc/src/sgml/ref/merge.sgml | 35 +-
doc/src/sgml/ref/update.sgml | 38 +-
doc/src/sgml/rules.sgml | 17 +
src/backend/executor/execExpr.c | 155 ++++++-
src/backend/executor/execExprInterp.c | 189 ++++++++-
src/backend/executor/execMain.c | 1 +
src/backend/executor/execUtils.c | 28 ++
src/backend/executor/nodeModifyTable.c | 223 ++++++++--
src/backend/jit/llvm/llvmjit_expr.c | 119 +++++-
src/backend/nodes/makefuncs.c | 12 +-
src/backend/nodes/nodeFuncs.c | 44 +-
src/backend/optimizer/path/allpaths.c | 1 +
src/backend/optimizer/plan/createplan.c | 20 +-
src/backend/optimizer/plan/setrefs.c | 15 +
src/backend/optimizer/plan/subselect.c | 45 +-
src/backend/optimizer/prep/prepjointree.c | 3 +-
src/backend/optimizer/util/appendinfo.c | 21 +-
src/backend/optimizer/util/clauses.c | 3 +
src/backend/optimizer/util/paramassign.c | 47 +++
src/backend/optimizer/util/plancat.c | 4 +-
src/backend/optimizer/util/var.c | 44 ++
src/backend/parser/analyze.c | 150 +++++--
src/backend/parser/gram.y | 57 ++-
src/backend/parser/parse_clause.c | 2 +
src/backend/parser/parse_expr.c | 18 +-
src/backend/parser/parse_merge.c | 4 +-
src/backend/parser/parse_relation.c | 33 +-
src/backend/parser/parse_target.c | 4 +-
src/backend/rewrite/rewriteHandler.c | 9 +
src/backend/rewrite/rewriteManip.c | 128 +++++-
src/backend/utils/adt/ruleutils.c | 113 +++--
src/include/catalog/catversion.h | 2 +-
src/include/executor/execExpr.h | 25 +-
src/include/executor/executor.h | 1 +
src/include/nodes/execnodes.h | 16 +
src/include/nodes/parsenodes.h | 52 ++-
src/include/nodes/plannodes.h | 2 +
src/include/nodes/primnodes.h | 40 ++
src/include/optimizer/optimizer.h | 1 +
src/include/optimizer/paramassign.h | 2 +
src/include/parser/analyze.h | 5 +-
src/include/parser/parse_node.h | 7 +
src/include/parser/parse_relation.h | 1 +
src/include/rewrite/rewriteManip.h | 1 +
src/interfaces/ecpg/preproc/parse.pl | 4 +-
src/test/isolation/expected/merge-update.out | 52 +--
src/test/isolation/specs/merge-update.spec | 4 +-
src/test/regress/expected/merge.out | 96 ++---
src/test/regress/expected/returning.out | 563 +++++++++++++++++++++++++
src/test/regress/expected/rules.out | 20 +-
src/test/regress/expected/updatable_views.out | 240 ++++++-----
src/test/regress/sql/merge.sql | 18 +-
src/test/regress/sql/returning.sql | 218 ++++++++++
src/test/regress/sql/rules.sql | 8 +-
src/test/regress/sql/updatable_views.sql | 50 ++-
src/tools/pgindent/typedefs.list | 6 +
61 files changed, 2910 insertions(+), 390 deletions(-)

Browse pgsql-committers by date

  From Date Subject
Next Message Álvaro Herrera 2025-01-16 15:46:38 pgsql: Split ATExecValidateConstraint into reusable pieces
Previous Message Peter Eisentraut 2025-01-16 13:43:58 pgsql: Remove dead code