PostgreSQL中使用FOREACH遍历数组执行查询报错的问题咨询

首先，你遇到的"cannot use RETURN QUERY in a non-SETOF function"错误根源很清晰：你用了DO匿名块编写逻辑，但DO块是没有返回值的，而RETURN QUERY只能在定义了返回SETOF（集合类型）的函数中使用——它的作用是将查询结果作为函数返回值输出，匿名块完全不支持这个语法。

下面给你三种可行的解决方案，你可以根据实际需求选择：

方案1：创建自定义函数返回结果集 #

这是最标准的方式，适合需要重复调用该逻辑的场景。我们定义一个返回指定结构的函数，在函数内部用FOREACH循环执行查询并返回结果：

CREATE OR REPLACE FUNCTION calculate_sku_metrics()
RETURNS TABLE(
    sku text,
    baseline numeric,
    promo_incremental numeric,
    coupon_incremental numeric
) AS $$
DECLARE
    a integer[] := array[1,2,3];
    i integer;
BEGIN
    FOREACH i IN ARRAY a LOOP
        RETURN QUERY
        SELECT 
            models.sku, 
            -- baseline计算逻辑
            (sum(models.unitretailprice) * sum(coefficients.unit_retail_price)) + 
            (sum(models.flag::int) * sum(coefficients.flag::int)) + 
            (sum(models.mc_baseline) * sum(coefficients.mc_baseline)) + 
            (sum(models.mc_day_avg) * sum(coefficients.mc_day_avg)) + 
            (sum(models.mc_day_normal) * sum(coefficients.mc_day_normal)) + 
            (sum(models.mc_week_avg) * sum(coefficients.mc_week_avg)) + 
            (sum(models.mc_week_normal) * sum(coefficients.mc_week_normal)) + 
            (sum(models.sku_day_avg) * sum(coefficients.sku_day_avg)) + 
            (sum(models.sku_month_avg) * sum(coefficients.sku_month_avg)) + 
            (sum(models.sku_month_normal)* sum(coefficients.sku_month_normal)) + 
            (sum(models.sku_moving_avg) * sum(coefficients.sku_moving_avg)) + 
            (sum(models.sku_week_avg) * sum(coefficients.sku_week_avg)) + 
            (sum(models.sku_week_normal)* sum(coefficients.sku_week_normal)) as baseline, 
            -- promoIncremental计算逻辑（用到循环变量i）
            (i * sum(coefficients.f)) + (5 * sum(coefficients.p)) + (0 * sum(coefficients.a)) as promoIncremental, 
            -- couponIncremnetal计算逻辑
            (sum(models.basket_dollar_off) * sum(coefficients.basket_dollar_off)) + 
            (sum(models.basket_per_off) * sum(coefficients.basket_per_off)) + 
            (sum(models.category_dollar_off) * sum(coefficients.category_dollar_off)) + 
            (sum(models.category_per_off) * sum(coefficients.category_per_off)) + 
            (sum(models.disc_per) * sum(coefficients.disc_per)) as couponIncremnetal 
        from models 
        join coefficients on models.sku = coefficients.sku and models.si_type = coefficients.si_type and models.model_type = coefficients.model_type 
        where coefficients.sku in ('12841276', '11873916') and coefficients.shop_descr = 'Papercrafting Technology' 
        group by models.sku;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

创建完成后，调用函数即可获取结果：

SELECT * FROM calculate_sku_metrics();

方案2：用临时表存储匿名块的执行结果 #

如果你只是临时执行一次逻辑，不想创建函数，可以用临时表存储循环中的查询结果，执行完匿名块后查询临时表即可：

DO $do$ 
DECLARE
    a integer[] := array[1,2,3];
    i integer;
BEGIN
    -- 创建临时表，会话结束后自动删除
    CREATE TEMP TABLE IF NOT EXISTS temp_sku_metrics (
        sku text,
        baseline numeric,
        promo_incremental numeric,
        coupon_incremental numeric
    ) ON COMMIT DROP;

    FOREACH i IN ARRAY a LOOP
        INSERT INTO temp_sku_metrics
        SELECT 
            models.sku, 
            -- 计算逻辑与方案1一致
            (sum(models.unitretailprice) * sum(coefficients.unit_retail_price)) + 
            (sum(models.flag::int) * sum(coefficients.flag::int)) + 
            (sum(models.mc_baseline) * sum(coefficients.mc_baseline)) + 
            (sum(models.mc_day_avg) * sum(coefficients.mc_day_avg)) + 
            (sum(models.mc_day_normal) * sum(coefficients.mc_day_normal)) + 
            (sum(models.mc_week_avg) * sum(coefficients.mc_week_avg)) + 
            (sum(models.mc_week_normal) * sum(coefficients.mc_week_normal)) + 
            (sum(models.sku_day_avg) * sum(coefficients.sku_day_avg)) + 
            (sum(models.sku_month_avg) * sum(coefficients.sku_month_avg)) + 
            (sum(models.sku_month_normal)* sum(coefficients.sku_month_normal)) + 
            (sum(models.sku_moving_avg) * sum(coefficients.sku_moving_avg)) + 
            (sum(models.sku_week_avg) * sum(coefficients.sku_week_avg)) + 
            (sum(models.sku_week_normal)* sum(coefficients.sku_week_normal)) as baseline, 
            (i * sum(coefficients.f)) + (5 * sum(coefficients.p)) + (0 * sum(coefficients.a)) as promoIncremental, 
            (sum(models.basket_dollar_off) * sum(coefficients.basket_dollar_off)) + 
            (sum(models.basket_per_off) * sum(coefficients.basket_per_off)) + 
            (sum(models.category_dollar_off) * sum(coefficients.category_dollar_off)) + 
            (sum(models.category_per_off) * sum(coefficients.category_per_off)) + 
            (sum(models.disc_per) * sum(coefficients.disc_per)) as couponIncremnetal 
        from models 
        join coefficients on models.sku = coefficients.sku and models.si_type = coefficients.si_type and models.model_type = coefficients.model_type 
        where coefficients.sku in ('12841276', '11873916') and coefficients.shop_descr = 'Papercrafting Technology' 
        group by models.sku;
    END LOOP;
END $do$;

-- 查询临时表获取结果
SELECT * FROM temp_sku_metrics;

方案3：用纯SQL替代FOREACH循环（更高效） #

其实你不需要用PL/pgSQL的循环，PostgreSQL可以用unnest()函数把数组转成行，再通过CROSS JOIN关联你的查询，纯SQL就能实现需求，性能比循环更好：

WITH input_values AS (
    -- 将数组转换为一行行的i值
    SELECT unnest(array[1,2,3]) AS i
)
SELECT 
    models.sku, 
    -- 计算逻辑不变
    (sum(models.unitretailprice) * sum(coefficients.unit_retail_price)) + 
    (sum(models.flag::int) * sum(coefficients.flag::int)) + 
    (sum(models.mc_baseline) * sum(coefficients.mc_baseline)) + 
    (sum(models.mc_day_avg) * sum(coefficients.mc_day_avg)) + 
    (sum(models.mc_day_normal) * sum(coefficients.mc_day_normal)) + 
    (sum(models.mc_week_avg) * sum(coefficients.mc_week_avg)) + 
    (sum(models.mc_week_normal) * sum(coefficients.mc_week_normal)) + 
    (sum(models.sku_day_avg) * sum(coefficients.sku_day_avg)) + 
    (sum(models.sku_month_avg) * sum(coefficients.sku_month_avg)) + 
    (sum(models.sku_month_normal)* sum(coefficients.sku_month_normal)) + 
    (sum(models.sku_moving_avg) * sum(coefficients.sku_moving_avg)) + 
    (sum(models.sku_week_avg) * sum(coefficients.sku_week_avg)) + 
    (sum(models.sku_week_normal)* sum(coefficients.sku_week_normal)) as baseline, 
    -- 关联input_values中的i值
    (iv.i * sum(coefficients.f)) + (5 * sum(coefficients.p)) + (0 * sum(coefficients.a)) as promoIncremental, 
    (sum(models.basket_dollar_off) * sum(coefficients.basket_dollar_off)) + 
    (sum(models.basket_per_off) * sum(coefficients.basket_per_off)) + 
    (sum(models.category_dollar_off) * sum(coefficients.category_dollar_off)) + 
    (sum(models.category_per_off) * sum(coefficients.category_per_off)) + 
    (sum(models.disc_per) * sum(coefficients.disc_per)) as couponIncremnetal 
from models 
join coefficients on models.sku = coefficients.sku and models.si_type = coefficients.si_type and models.model_type = coefficients.model_type
-- 交叉连接数组转成的行，相当于遍历每个i值
cross join input_values iv
where coefficients.sku in ('12841276', '11873916') and coefficients.shop_descr = 'Papercrafting Technology' 
group by models.sku, iv.i;

这种方式不需要编写PL/pgSQL代码，执行效率更高，推荐优先使用。

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