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
All I can say is: finally! We've been waiting for this for so long.
We got RETURNING clause to insert/update/delete back in 2006, in version 8.2.
While it was great it lacked the ability to return value before change (in case of update).
Consider:
=$ UPDATE t SET v = 123 WHERE id IN (1,2,3) RETURNING id, v; id │ v ────┼───── 1 │ 123 2 │ 123 3 │ 123 (3 ROWS)
Which is great, but in some cases it would be good to be able to return the value that was there before update.
Sure, I could have done it with CTEs, and query that first selects (for update), and then update, but it's error prone, tedious, and harder to read:
=$ WITH x AS (SELECT id, v FROM t WHERE id IN (1,2,3) FOR UPDATE), u AS (UPDATE t SET v = 123 FROM x WHERE t.id = x.id returning t.id, t.v) SELECT COALESCE(x.id, u.id), x.v AS BEFORE, u.v AS after FROM x FULL OUTER JOIN u USING (id); COALESCE │ BEFORE │ after ──────────┼────────┼─────── 1 │ 1 │ 123 2 │ 5 │ 123 3 │ 10 │ 123 (3 ROWS)
Luckily, this problem is now gone.
I can just do:
=$ UPDATE t SET v = least(123 * v, 200) WHERE id IN (1,2,3) returning id, OLD.v, NEW.v; id │ v │ v ────┼────┼───── 1 │ 1 │ 123 2 │ 5 │ 200 3 │ 10 │ 200 (3 ROWS)
Amazing! Thank you, very much, to everyone involved.