PostgerSQL使用问答04 - Returning

本文的主要内容是讨论Postgres的retuning 子句。

什么是Returning子句

Retuning子句是PostreSQL提供的一个SQL语句和特性。它可以用于在DML语句中，返回修改后的数据行。这个特性在Web应用开发中可能会非常有用，它可以极大的简化代码和操作。

例如，对于一个拥有自增字段的表，插入一条记录，插入后想要获取这条记录包括自增ID的话，传统的开发模式，可能需要进行额外的操作，例如使用插入数据进行查询或者类似MySQL中，LAST_INSERT_ID()这种方法。都需要执行多次查询，显得比较繁琐。

这时，使用Returning子句，就可以在插入数据后就获得这个插入的数据包括自增ID的值，非常简单和方便。此外，如果批量插入，又希望获得这些插入的记录，好像也只有这种方式满足要求。

Returing不是独立的SQL语句，而是一个子句，以Insert语句为例，在其中Returning子句的标准形式是(来自PostgreSQL官方技术文档)：

sql

复制代码

[ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ] [ OVERRIDING { SYSTEM | USER } VALUE ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ ON CONFLICT [ conflict_target ] conflict_action ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

就是说，RETURING可以支持返回 * (所有字段)，或者输出表达式(字段或者计算字段)，并可以使用别名。

补充一下，在新的MySQL8版本中，也实现和支持Returning子句。

举个栗子？

我们先以插入数据也是Returning最常用的场景作为示例，如使用如下的数据表和查询：

js

复制代码

// 创建表 CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, passwd VARCHAR(100) NOT NULL, upd_at int[] ); // 插入并返回记录 insert into users(username,passwd,upd_at) values ('tom','','{0,1}') returning *; id | username | passwd | upd_at ----+----------+--------+-------- 8 | tom | | {0,1} (1 row) INSERT 0 1 // 插入并返回特定字段 insert into users(username,passwd,upd_at) values ('david','','{0,1}') returning id, username; // 插入并返回特定字段的别名 insert into users(username,passwd,upd_at) values ('smith','','{0,1}') returning id, username, upd_at[1] as create_at;

这个示例中，我们先用通常的方式，创建了一个数据表。指定主键ID是一个序列(自增整数)。然后插入仪表记录，这时虽然没有指定ID的值，但使用Returning子句，就可以返回创建完成后当前记录的值，包括ID。

Returning可以返回当前记录的值，也可以指定所返回记录的字段，这也是推荐的方式，因为使用 * 来表示所有字段，可能会增加数据库处理的负担，并且增加不确定性，增加产生无法预计结果的机会。

在Returning子句中也可以使用 as 关键字，修改输出字段的名称。

Update和Delete也可以吗？

当然可以，returning子句支持Insert、Update和Delete这些修改数据的操作类型。

sql

复制代码

// 删除并返回删除记录 delete from users where username='joe' returning *; id | username | passwd | upd_at ----+----------+--------+-------- 4 | joe | | {0,1} (1 row) DELETE 1 // 更新并返回更新的记录 update users set upd_at[1] = 100 where username='john' returning *; id | username | passwd | upd_at ----+----------+--------+-------- 2 | john | | {100} (1 row) UPDATE 1

有什么需要注意的问题吗？

关于Retunring，笔者觉得有以下一些要点和需要注意的问题：

• RETURNING子句结合了数据修改操作和操作结果的查询两个操作，本质上是一个语法糖
• RETURNING是数据操作语句的一部分，修改和查询是一个原子化操作，可以保证更新和返回数据的一致性
• 通常用于简化操作和查询编程
• RUTURING子句也可以用于服务端语言如PL/pgSQL
• RETURING并不限制在修改的字段和数据，而是涉及修改的记录
• 需要注意其返回值是修改涉及的记录值，而不是当前记录值，它们可能一致，也可能不一致(如Delete操作)
• 从技术文档上来看，似乎Returning不支持Postgres新的Merge语句？