PostgreSQL 17新特性merge语法增强

PostgreSQL 17 merge语法增强，支持 RETURNING 子句，可以返回新增、更新或者删除的数据行

参考官方文档
https://www.postgresql.org/docs/17/sql-merge.html

1、merge语法

[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]
[ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]

where data_source is:

{ [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]

and when_clause is:

{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN { merge_insert | DO NOTHING } }

and merge_insert is:

INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

and merge_update is:

UPDATE SET { column_name = { expression | DEFAULT } |
             ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
             ( column_name [, ...] ) = ( sub-SELECT )
           } [, ...]

and merge_delete is:

DELETE

2、示例

superdb=# create table t_testpg17(id int primary key,nameinfo varchar(32));
CREATE TABLE

superdb=# \d
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 public | t_testpg17 | table | super
(1 row)

superdb=# insert into t_testpg17 values(1,'rc1');
INSERT 0 1
superdb=# insert into t_testpg17 values(2,'pg17');
INSERT 0 1
superdb=# select * from t_testpg17;
 id | nameinfo
----+----------
  1 | rc1
  2 | pg17
(2 rows)

merge into t_testpg1
using (values(2,'postgresql 17 rc1'),(3,'merge'),(4,'rerurning')) t2 (id,nameinfo) on t1.id=t2.id
when matched then 
  update set nameinfo=t2.nameinfo
when not matched then 
  insert(id,nameinfo) values(t2.id,t2.nameinfo)
returning merge_action(), *;
-- run result
 merge_action | id |     nameinfo      | id |     nameinfo
--------------+----+-------------------+----+-------------------
 UPDATE       |  2 | postgresql 17 rc1 |  2 | postgresql 17 rc1
 INSERT       |  4 | rerurning         |  4 | rerurning
 INSERT       |  3 | merge             |  3 | merge