如何使用FOREACH对数组中的值进行多次查询

Question

我想运行一个查询，并通过在postgresql中使用FOREACH循环遍历数组来获得行。

我尝试按照文档中的说明使用FOREACH循环，但它返回错误"cannot use RETURN QUERY in a non-SETOF function“

 DO
  $do$
  DECLARE
  a integer[] := array[1,2,3];
  i integer;   
 begin
  foreach i IN ARRAY a
 LOOP 
RETURN QUERY
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,
(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;

结束$do$

Answer 1

您可以将具有因子的CTE交叉联接到由当前表组成的派生表。

WITH a (i)
AS
(
VALUES (1),
       (2),
       (3)
)
SELECT ...
       a.i * x.sum_f + 5 * x.sum_p promoincremental,
       ...
       FROM a
            CROSS JOIN (SELECT ...
                               sum(coefficients.f) sum_f,
                               sum(coefficients.p) sum_p,
                               ...
                               FROM ...) x;