PostgreSQL循环查询结果集并执行嵌套插入的实现指导

我来帮你修正这个PL/pgSQL函数，解决语法和逻辑上的问题，同时给你一些优化建议：

你的需求很清晰：从nok.commission_expenses里筛选出已设置cost_item_id和purchase_id的记录，关联transactions表中target_id匹配该purchase_id的交易，然后基于这些关联数据插入新的交易记录。下面是修正后的完整函数，我会标注关键的修正点：

CREATE OR REPLACE FUNCTION loop_and_create() RETURNS VOID AS $$
DECLARE
    rec RECORD;
    tx RECORD; -- 修正变量名，和循环变量保持一致
BEGIN
    -- 修正点1：不用EXECUTE，直接用静态SELECT遍历记录，更高效且避免变量引用问题
    FOR rec IN 
        SELECT * FROM nok.commission_expenses ce 
        WHERE ce.cost_item_id IS NOT NULL AND ce.purchase_id IS NOT NULL
    LOOP
        -- 修正点2：同样用静态SELECT关联，直接引用rec.purchase_id即可
        FOR tx IN 
            SELECT * FROM transactions t 
            WHERE t.target_id::integer = rec.purchase_id
        LOOP
            -- 修正点3：用标准INSERT语法，明确指定列名，避免结构变更导致的错误
            INSERT INTO transactions (
                id, user_id, transaction_type, account, 
                amount, target_id, target_type, created_at, 
                updated_at, log_id
            ) VALUES (
                nextval('transactions_id_seq'::regclass), 
                tx.user_id, 
                tx.transaction_type, 
                tx.account, 
                rec.amount, 
                rec.id, 
                tx.target_type, 
                tx.created_at, 
                tx.updated_at, 
                tx.log_id
            );
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

关键修正说明 #

1. 移除不必要的EXECUTE：你的SQL都是静态的（没有动态表名/列名），直接用FOR IN SELECT的方式更简洁，还能直接引用PL/pgSQL变量（比如rec.purchase_id）。如果非要用EXECUTE，得用USING传参（比如EXECUTE 'SELECT ... WHERE t.target_id::integer = $1' USING rec.purchase_id），但这里完全没必要。
2. 修复INSERT语法：原语句缺了列名列表和VALUES关键字，这是PostgreSQL INSERT的硬性要求。明确列名不仅能解决语法错误，还能防止未来表结构变更（比如加新列）导致插入失败。
3. 统一变量名：原代码里DECLARE定义的是txt RECORD，但循环用了tx，这会编译报错，现在统一成tx RECORD。

性能优化建议 #

如果你的表数据量较大，嵌套循环的PL/pgSQL函数效率可能不高。推荐用单条INSERT...SELECT语句实现相同逻辑，避免逐行循环的开销，示例如下：

INSERT INTO transactions (
    id, user_id, transaction_type, account, 
    amount, target_id, target_type, created_at, 
    updated_at, log_id
)
SELECT
    nextval('transactions_id_seq'::regclass),
    t.user_id,
    t.transaction_type,
    t.account,
    ce.amount,
    ce.id,
    t.target_type,
    t.created_at,
    t.updated_at,
    t.log_id
FROM nok.commission_expenses ce
JOIN transactions t ON t.target_id::integer = ce.purchase_id
WHERE ce.cost_item_id IS NOT NULL AND ce.purchase_id IS NOT NULL;

这种基于集合的操作比循环快得多，除非你有必须用循环的特殊业务逻辑（比如每插入一条要做复杂判断或额外操作），否则优先用这种方式。

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