如何实现接收其他函数返回结果的PostgreSQL Upsert函数

问题核心原因 #

你遇到的function values_upsert(record[]) does not exist错误，本质是类型不兼容：自定义函数期望接收target[]类型的数组，但calc()返回的是通用record集合，聚合后得到record[]，两者无法自动转换。

分步解决方案 #

1. 确保类型匹配（两种可选方式） #

方式一：调用时显式转换类型

如果calc()返回的结构和target表完全一致，调用时先将结果转换为target类型再聚合为数组：

-- 调用Upsert函数，先转换类型再聚合
SELECT values_upsert(ARRAY(SELECT calc()::target));

方式二：修改函数参数为集合类型（更适合百万级批量）

数组类型存在大小限制，百万条数据可能触发容量问题，建议直接将函数参数改为SETOF target（和calc()的返回类型匹配）：

CREATE OR REPLACE FUNCTION values_upsert(new_records SETOF target)
RETURNS VOID AS $$
BEGIN
    -- 核心逻辑见下文
END;
$$ LANGUAGE plpgsql;

-- 调用时直接传入calc()结果，无需手动聚合
SELECT values_upsert(calc());

2. 实现Upsert+历史归档的核心逻辑 #

以下是针对百万级数据优化的函数实现，先归档旧记录再执行批量Upsert：

CREATE OR REPLACE FUNCTION values_upsert(new_records SETOF target)
RETURNS VOID AS $$
BEGIN
    -- 步骤1：将目标表中即将被更新的旧版本记录插入历史表
    INSERT INTO target_h (id, data, version)
    SELECT t.id, t.data, t.version
    FROM target t
    JOIN new_records nr ON t.id = nr.id
    WHERE t.version < nr.version; -- 仅归档比新记录版本旧的数据

    -- 步骤2：批量Upsert到目标表
    INSERT INTO target (id, data, version)
    SELECT nr.id, nr.data, nr.version
    FROM new_records nr
    ON CONFLICT (id) DO UPDATE
    SET data = EXCLUDED.data,
        version = EXCLUDED.version
    WHERE target.version < EXCLUDED.version; -- 仅更新旧版本记录
END;
$$ LANGUAGE plpgsql;

3. 适配calc()返回非target类型的场景 #

如果calc()返回的结构和target表不一致，调用时需要手动映射字段转换为target类型：

-- 假设calc()返回(id int, new_data text)，手动补充version字段
SELECT values_upsert(
    SELECT (r.id, r.new_data, 2)::target 
    FROM calc() r
);

百万级数据性能优化建议 #

• 索引优化：确保target表的id为主键，target_h表建立(id, version)联合主键，避免归档时的全表扫描。
• 避免循环处理：使用JOIN和批量INSERT替代逐条记录处理，大幅提升效率。
• 临时禁用非必要约束：如果归档和Upsert过程中不需要实时触发其他逻辑，可以临时禁用非必要约束，完成后恢复。

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