How to use SELECT INTO multi variables with FOR LOOP in POSTGRESQL PROCEDURE-CodePudding

I'm trying to write a procedure in PostgreSQL to update number of products sold from Order_items table to Stock table. Here's my query

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;
$$;

It keeps saying that i have syntax error at INTO v_pid,

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

How could i fix it? Thank you guys.

CodePudding user response：

Use a CTE and no loop is needed:

CREATE OR REPLACE PROCEDURE smallerp.sp_calculate_stock ()
LANGUAGE sql
AS $$
WITH cte AS (
        SELECT i.product_id, o.store_id, SUM(i.quantity) as 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
    )
    UPDATE smallerp.p_stocks kk
    SET (kk.product_id, kk.store_id, kk.sold) = (cte.product_id, cte.store_id, cte.sold)
    FROM cte
    WHERE kk.store_id = cte.store_id AND kk.product_id = cte.product_id;
$$;

You don't need plpgsql either, sql is good enough.

CodePudding user response：

Frank's answer is the correct solution for the underlying problem. To answer the syntax question: if you use a FOR loop the all columns are available through the record (loop) variable. So get rid of the INTO in the SELECT of the FOR loop, then reference the columns from the record variable:

DECLARE 
  f record;
BEGIN
  FOR f IN
      SELECT i.product_id, o.store_id, SUM(i.quantity) as 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) = (f.product_id, f.store_id, f.sold)
      WHERE kk.store_id = _sid AND kk.product_id = _pid;
      
  END LOOP;
END;

But again: doing an UPDATE in a LOOP is typically not a good idea.