RETURNING 子句

概念概述

RETURNING 子句（The RETURNING Clause）指的是：RETURNING 让修改语句回传受影响行的列值，省去插入或更新后再查询一次的额外往返，尤其便于获取生成的标识。

从更严格的定义看，RETURNING is a SQL extension on INSERT, UPDATE, and DELETE that returns expressions computed from the rows the statement inserted, updated, or removed. 该概念在工程实践中的价值，在于它能帮助团队用统一的语言描述一个具体的质量属性或设计约束，从而在评审与协作中减少歧义。

为什么需要它

It is used to fetch generated identifiers, defaults, and server-side computed values immediately after a write. 它与 GIN Indexes for Composite Values、Trigram Indexes for Fuzzy Matching、Full-Text Search Vectors 等概念经常一起出现，用于解决相近的工程问题。理解它的适用场景，有助于在多种备选方案之间做出有依据的取舍，而不是凭直觉选择技术方案。

常见误解

• returning returns every row that matched the update
• the clause is available on every database engine

需要避免的反模式

• issuing a second select to retrieve a just-generated key
• using returning inside a statement that must run on a database lacking support

实践要点

1. For inserts it avoids a follow-up query for a generated primary key or a default such as a creation timestamp, halving the round trips for that write.
2. For updates and deletes it returns only the rows actually affected, which is valuable when the statement has a WHERE clause that might match nothing.
3. Portability varies, so code intended for more than one engine should isolate the clause behind a repository layer rather than spreading it across the schema.

参考来源

https://www.postgresql.org/docs/current/dml-returning.html

代码示例

-- 乐观锁更新：版本匹配才写入，且不允许透支。
UPDATE accounts
SET balance = balance - :amount,
    version = version + 1
WHERE id = :account_id
  AND version = :expected_version
  AND balance >= :amount;

-- 应用侧检查受影响行数：
--   1  -> 更新成功
--   0  -> 并发冲突，或余额不足（需再读一次以区分）

-- 取回最新版本用于重试：
SELECT id, balance, version FROM accounts WHERE id = :account_id;

package store

import (
	"context"
	"database/sql"
)

type User struct {
	ID    int64
	Email string
}

func ListActiveUsers(ctx context.Context, db *sql.DB) ([]User, error) {
	rows, err := db.QueryContext(ctx,
		"SELECT id, email FROM users WHERE active = $1 ORDER BY id", true)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var user User
		if err := rows.Scan(&user.ID, &user.Email); err != nil {
			return nil, err
		}
		users = append(users, user)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return users, nil
}