Waiting for PostgreSQL 18 – Add OLD/NEW support to RETURNING in DML queries.

On 16th of January 2025, Dean Rasheed committed patch:

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

Continue reading Waiting for PostgreSQL 18 – Add OLD/NEW support to RETURNING in DML queries.

Waiting for PostgreSQL 10 – Implement syntax for transition tables in AFTER triggers.

Another one missed, quite a long time ago, too..:

On 4th of November 2016, Kevin Grittner committed patch:

Implement syntax for transition tables in AFTER triggers.
 
 
This is infrastructure for the complete SQL standard feature.  No
support is included at this point for execution nodes or PLs.  The
intent is to add that soon.
 
As this patch leaves things, standard syntax can create tuplestores
to contain old and/or new versions of rows affected by a statement.
References to these tuplestores are in the TriggerData structure.
C triggers can access the tuplestores directly, so they are usable,
but they cannot yet be referenced within a SQL statement.

Continue reading Waiting for PostgreSQL 10 – Implement syntax for transition tables in AFTER triggers.