如何在INSERT语句中获取所有新增行的序列值数组？

问题描述 #

我有一条INSERT语句，可向表中插入单条或多条记录，需要获取每一行新增记录的主键ID值。尝试在INSERT语句中添加以下代码：

RETURNING array_agg(new_seq_id) INTO new_ids

但出现报错：

[42803] ERROR: aggregate functions are not allowed in RETURNING

请问是否有办法通过RETURNING语句获取所有新增序列值的数组？

解决方法 #

PostgreSQL的RETURNING子句不支持直接使用聚合函数，但可以通过两种方式实现需求：

方法一：利用临时表聚合结果（适合PL/pgSQL场景） #

先通过RETURNING将所有新增ID写入临时表，再从临时表聚合为数组：

DECLARE
    new_ids INT[];
BEGIN
    INSERT INTO your_table (col1, col2)
    VALUES ('val1', 'val2'), ('val3', 'val4')
    RETURNING new_seq_id INTO TEMP TABLE temp_ids;
    
    SELECT array_agg(new_seq_id) INTO new_ids FROM temp_ids;
END;

方法二：使用ARRAY()构造函数包裹返回结果 #

直接用ARRAY()将RETURNING返回的多行ID组装成数组，这种方式更简洁，适用于普通SQL或PL/pgSQL：

-- 普通SQL查询场景
SELECT ARRAY(
    INSERT INTO your_table (col1, col2)
    VALUES ('val1', 'val2'), ('val3', 'val4')
    RETURNING new_seq_id
);

-- PL/pgSQL场景
DECLARE
    new_ids INT[];
BEGIN
    SELECT ARRAY(
        INSERT INTO your_table (col1, col2)
        VALUES ('val1', 'val2'), ('val3', 'val4')
        RETURNING new_seq_id
    ) INTO new_ids;
END;

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