是否应该使用PL/pgSQL遍历表而非直接使用SQL处理数据同步？

可行性结论 #

你描述的同步场景完全可以使用PL/pgSQL循环遍历处理，该方案对复杂的逐行同步逻辑适配性很高。

实现示例 #

以下是和你设想的伪代码逻辑对应的可运行PL/pgSQL示例，你可以直接在循环内扩展自己的增删改逻辑：

DO $$
DECLARE
    row_data record; -- 存储每一轮循环的关联行数据
BEGIN
    -- 预处理：先给临时表去重，避免后续重复处理
    DELETE FROM tmp a 
    USING tmp b 
    WHERE a.id = b.id AND a.ctid < b.ctid;

    -- 遍历ID匹配的临时表和主表数据，需要处理新插入场景可换成LEFT JOIN
    FOR row_data IN
        SELECT t.*, m.*
        FROM tmp t
        INNER JOIN mytable m ON t.id = m.id
    LOOP
        -- 在这里写你的自定义逻辑，示例如下：
        -- 字段值满足条件时更新
        IF row_data.tmp_field > 0 THEN
            UPDATE mytable 
            SET field = row_data.tmp_field + 1,
                update_time = now()
            WHERE id = row_data.id;
        END IF;
        -- 标记删除的场景同步删除主表数据
        IF row_data.is_deleted = true THEN
            DELETE FROM mytable WHERE id = row_data.id;
        END IF;
    END LOOP;

    -- 额外补充：处理临时表有但主表没有的新数据，不需要可删除
    INSERT INTO mytable (id, field, create_time)
    SELECT t.id, t.field, now()
    FROM tmp t
    LEFT JOIN mytable m ON t.id = m.id
    WHERE m.id IS NULL;
END $$;

适用场景与注意事项 #

• 推荐使用循环的场景：同步逻辑包含大量逐行判断、自定义函数调用、多表联动操作，用纯批量SQL写起来逻辑晦涩、维护成本极高。
• 性能风险提示：如果临时表数据量超过10万行，逐行循环的执行效率会远低于纯批量SQL操作，这种情况建议尽量把通用逻辑拆成批量UPDATE/INSERT/DELETE实现，仅把无法批量处理的特殊逻辑放在循环中。

优化建议 #

• 提前给临时表的关联字段ID创建索引，可大幅提升关联查询的速度：CREATE INDEX idx_tmp_id ON tmp(id);
• 尽量合并循环内的SQL操作，减少单轮循环的SQL执行次数
• 数据量极大时可按ID范围分批处理，避免长事务持有锁时间过长影响其他业务

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