PostgreSQL存储过程：FOR LOOP中SELECT INTO多变量语法错误修复

问题场景 #

编写存储过程，将s_order_items表中的商品售出数量汇总后更新至p_stocks表，但执行代码时触发语法错误。

原代码 #

CREATE OR REPLACE PROCEDURE smallerp.sp_calculate_stock ()
LANGUAGE plpgsql
AS $$
DECLARE f record;
        v_pid integer;
        v_sid integer;
        v_sold integer;
BEGIN

FOR f IN
    (SELECT i.product_id, o.store_id, SUM(i.quantity)
    INTO v_pid, v_sid, v_sold)
    FROM smallerp.s_order_items i
    INNER JOIN smallerp.s_orders o
        ON i.order_id = o.order_id
    INNER JOIN smallerp.p_products r
        ON i.product_id = r.product_id
    INNER JOIN smallerp.s_stores s 
        ON o.store_id = s.store_id
    GROUP BY o.store_id, i.product_id, s.store_name, r.product_name 
    ORDER BY s.store_name
    
LOOP    

    UPDATE smallerp.p_stocks kk
    SET (kk.product_id,
         kk.store_id,
         kk.sold)
         = (v_pid, v_sid, v_sold)
        WHERE kk.store_id = _sid AND kk.product_id = _pid;
END LOOP;

END;
$$;

报错信息 #

ERROR:  syntax error at or near ","
LINE 16:  INTO v_pid, v_sid, v_sold)

错误原因及修复方案 #

1. FOR循环中错误使用INTO：FOR f IN语句本身会将查询结果的每条记录赋值给f变量，不需要在SELECT里额外加INTO子句，这是语法错误的根源。
2. 未定义的变量引用：UPDATE语句中使用的_sid、_pid未声明，且原代码中v_pid、v_sid、v_sold也没有被正确赋值。
3. 不必要的字段更新：p_stocks表的product_id和store_id是关联主键，不需要在UPDATE时重新赋值，只需要更新sold字段即可。

修复后的代码 #

CREATE OR REPLACE PROCEDURE smallerp.sp_calculate_stock ()
LANGUAGE plpgsql
AS $$
DECLARE f record;
BEGIN
    -- 循环遍历按门店、商品分组的售出量汇总结果
    FOR f IN
        SELECT 
            i.product_id, 
            o.store_id, 
            SUM(i.quantity) AS total_sold
        FROM smallerp.s_order_items i
        INNER JOIN smallerp.s_orders o
            ON i.order_id = o.order_id
        INNER JOIN smallerp.p_products r
            ON i.product_id = r.product_id
        INNER JOIN smallerp.s_stores s 
            ON o.store_id = s.store_id
        GROUP BY o.store_id, i.product_id, s.store_name, r.product_name 
        ORDER BY s.store_name
    LOOP    
        -- 更新对应门店商品的售出数量
        UPDATE smallerp.p_stocks kk
        SET sold = f.total_sold
        WHERE kk.store_id = f.store_id 
          AND kk.product_id = f.product_id;
    END LOOP;
END;
$$;

额外优化建议 #

如果数据量较大，单条循环更新效率较低，可以改用批量更新的方式，避免循环操作：

CREATE OR REPLACE PROCEDURE smallerp.sp_calculate_stock ()
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE smallerp.p_stocks kk
    SET sold = agg.total_sold
    FROM (
        SELECT 
            i.product_id, 
            o.store_id, 
            SUM(i.quantity) AS total_sold
        FROM smallerp.s_order_items i
        INNER JOIN smallerp.s_orders o
            ON i.order_id = o.order_id
        INNER JOIN smallerp.p_products r
            ON i.product_id = r.product_id
        INNER JOIN smallerp.s_stores s 
            ON o.store_id = s.store_id
        GROUP BY o.store_id, i.product_id, s.store_name, r.product_name
    ) agg
    WHERE kk.store_id = agg.store_id 
      AND kk.product_id = agg.product_id;
END;
$$;

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