PostgreSQL中如何将SQL查询返回值存入变量并实现关联插入

针对你的需求，PostgreSQL提供两种常用方案，解决第一个INSERT返回值复用的问题：

方法一：使用WITH子句（CTE）完成原子操作 #

这种方式用单条SQL语句完成两个插入，保证原子性，适合db-migrate脚本：

WITH inserted_user AS (
  INSERT INTO users(firstname, lastname) VALUES ('John', 'Smith')
  RETURNING id
)
INSERT INTO roles(role, user_id)
SELECT 'ROLE_USER', id FROM inserted_user;

原理是把第一个INSERT的返回结果临时存在inserted_user公共表表达式里，第二个INSERT直接从这个临时集合中读取id。

方法二：使用PL/pgSQL匿名块（适合多步骤操作） #

如果需要多次复用返回的id，可以用PostgreSQL的匿名函数块，注意PostgreSQL不支持DECLARE @变量名这种SQL Server风格的变量声明，正确写法如下：

DO $$
DECLARE
  new_user_id INTEGER; -- 声明变量，无需加@
BEGIN
  -- 插入用户并将返回的id存入变量
  INSERT INTO users(firstname, lastname) VALUES ('John', 'Smith')
  RETURNING id INTO new_user_id;

  -- 使用变量插入角色关联
  INSERT INTO roles(role, user_id)
  VALUES ('ROLE_USER', new_user_id);
END $$;

你之前遇到的语法错误，是因为误用了SQL Server的变量声明语法，PostgreSQL的变量声明不需要加@，直接在DECLARE块里指定变量名和类型即可。

内容的提问来源于stack exchange，提问作者Luk