PostgreSQL 18：在 DML 查询中添加对 RETURNING 的 OLD/NEW 支持

在对OpenStack进行一些研究时（请参阅此处的介绍文章），我认为写一篇关于 PostgreSQL 下一版本新功能的博客可能会让我从所有这些 OpenStack 服务中解放出来，所以我们就开始吧 🙂

您可以在 PostgreSQL 中执行的操作是返回插入/更新或删除语句的值，如下所示：

postgres=# create table t ( a int, b text );
CREATE TABLE
postgres=# insert into t values (1,'aaa') returning *;
 a |  b  
---+-----
 1 | aaa
(1 row)
 
INSERT 0 1
postgres=# update t set b = 'bbb' returning *;
 a |  b  
---+-----
 1 | bbb
(1 row)
 
UPDATE 1
 
postgres=# delete from t where b = 'bbb' returning *;
 a |  b  
---+-----
 1 | bbb
(1 row)
 
DELETE 1

这对于从一开始就可能不知道的生成值或默认值特别有用：

postgres=# drop table t;
DROP TABLE
postgres=# create table t ( a int primary key generated always as identity
                          , b text
                          , c timestamptz default now() );
CREATE TABLE
postgres=# insert into t (b) values ('aaa') returning a,c;
 a |               c               
---+-------------------------------
 1 | 2025-01-22 10:22:13.738709+01
(1 row)
 
INSERT 0 1

截至目前，您无法同时引用（或返回）受影响行的旧值和新值。使用此补丁后，情况发生了变化，您可以同时返回旧值和新值：

ostgres=# update t set b = 'xxx' returning old.b as old, new.b as new;
 old | new 
-----+-----
 aaa | xxx
(1 row)
 
UPDATE 1

对于插入语句，旧值将为空；对于删除语句，新值将为空：

postgres=# insert into t (b) values ('yyy') returning old.b as old, new.b as new;
old | new
----+-----
    | yyy
(1 row)
 
INSERT 0 1
postgres=# delete from t where b = 'yyy' returning old.b as old, new.b as new;
 old | new 
-----+-----
 yyy | 
(1 row)
 
DELETE 1

这对于多行也有效（并且以前也有效）：

postgres=# insert into t (b) select md5(i::text) from generate_series(1,10) i;
INSERT 0 10
postgres=# update t set b = 'qqq' returning old.b as old, new.b as new;
               old                | new 
----------------------------------+-----
 xxx                              | qqq
 c4ca4238a0b923820dcc509a6f75849b | qqq
 c81e728d9d4c2f636f067f89cc14862c | qqq
 eccbc87e4b5ce2fe28308fd9f2a7baf3 | qqq
 a87ff679a2f3e71d9181a67b7542122c | qqq
 e4da3b7fbbce2345d7772b0674a318d5 | qqq
 1679091c5a880faf6fb5e6087eb1b2dc | qqq
 8f14e45fceea167a5a36dedd4bea2543 | qqq
 c9f0f895fb98ab9159f51fd0297e236d | qqq
 45c48cce2e2d7fbdea1afc51c7c6ad26 | qqq
 d3d9446802a44259755d38e6d163e820 | qqq
(11 rows)
 
UPDATE 11

原文地址：https://www.dbi-services.com/blog/postgresql-18-add-old-new-support-to-returning-in-dml-queries/
原文作者：Daniel Westermann