PostgreSQL函数中INSERT...RETURNING返回多行报错求助

报错ERROR: query returned more than one row的原因是：你试图用RETURNING ... INTO inserted_ids将INSERT语句返回的多行ID直接赋值给数组变量，但PostgreSQL中这种写法仅支持单行返回值。当INSERT插入多条记录时，RETURNING返回多行，这种赋值方式会触发错误。

方案一：直接返回INSERT的RETURNING结果（推荐） #

无需中间数组变量，直接将INSERT操作的RETURNING结果作为函数的返回数据集，代码更简洁高效：

CREATE OR REPLACE FUNCTION schema.copy_data(arg_client_id CHAR(30))
RETURNS TABLE (dest_id CHAR(30))
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
BEGIN
    RETURN QUERY
    WITH
    cte_1 AS (...),
    cte_2 AS (...)
    INSERT INTO schema.dest
    (client_id, fk_id, ...)
    SELECT client_id, fk_id, ...
    FROM cte_1
    WHERE NOT EXISTS(
        SELECT 1 FROM cte_2
        WHERE cte_1.client_id = cte_2.client_id
          AND cte_1.fk_id = cte_2.fk_id
          AND ...
    )
    RETURNING schema.dest.dest_id;
END
$$;

方案二：通过数组变量存储ID（如需额外处理） #

如果必须将插入的ID存储到数组变量中做后续处理，需要用ARRAY_AGG将RETURNING返回的多行ID聚合为数组后再赋值：

CREATE OR REPLACE FUNCTION schema.copy_data(arg_client_id CHAR(30))
RETURNS TABLE (dest_id CHAR(30))
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE 
    inserted_ids CHAR(30)[];
BEGIN
    WITH
    cte_1 AS (...),
    cte_2 AS (...)
    SELECT ARRAY_AGG(dest_id) INTO inserted_ids
    FROM (
        INSERT INTO schema.dest
        (client_id, fk_id, ...)
        SELECT client_id, fk_id, ...
        FROM cte_1
        WHERE NOT EXISTS(
            SELECT 1 FROM cte_2
            WHERE cte_1.client_id = cte_2.client_id
              AND cte_1.fk_id = cte_2.fk_id
              AND ...
        )
        RETURNING schema.dest.dest_id
    ) AS inserted_records;

    RETURN QUERY SELECT dest_id FROM UNNEST(inserted_ids) AS dest_id;
END
$$;

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