Postgres cursor is not taking multiple parameters

Question

I have created a procedure, and inside the procedure I have created a parameterized cursor to which I am passing parameters. The Cursor query has two parameters: one parameter of the procedure and the other is the parameter of the cursor.

When I am opening the cursor with the parameter of the cursor, it is not taking the value of the procedure parameter. When I am creating a cursor with no parameter, it is taking the parameter of the procedure.

See the example below: c2 opens just fine and inserts data into v_text, but when I am trying to open c9 it gives me the error

ERROR:  column "p_plan_version_id" does not exist
HINT:  Perhaps you meant to reference the column "pos.plan_version_id"

CREATE OR REPLACE PROCEDURE create_file(IN p_plan_version_id NUMERIC) LANGUAGE plpgsql
AS $procedure$
    DECLARE
        v_text text;
        j record;
        l record;
        c2 CURSOR FOR
            SELECT pq.product_code,
                   SUM(quantity) quantity
            FROM product_quantity pq,
                 products         ep
            WHERE pq.plan_version_id = p_plan_version_id
            --and pipeline_id  =1
              AND     pq.product_code = ep.product_code
            GROUP BY product_code;

        C9 CURSOR (p_product_code VARCHAR) IS
            SELECT product_code ,
                   terminal_code,
                   quantity
            FROM product_stock pos
            WHERE pos.plan_version_id = p_plan_version_id
              AND pos.product_code    = p_product_code;

    BEGIN
        OPEN c2;
        --for j in C2
        LOOP
            FETCH c2 INTO j;
            EXIT WHEN NOT found;

            v_text := j.product_code
                      || ','
                      || j.quantity
                      || ',,,,,,,,,,,,,,,,,,,,';
        END LOOP;

        CLOSE c2;

        OPEN c9('OIL');
        LOOP
            FETCH c9 INTO l;
            EXIT WHEN NOT found;
 
            v_text = v_text
                     || l.product_code
                     || ','
                     || l.terminal_code
                     || ','
                     || l.quantity
                     || ','
                     || ',,,,,,,,,,,,,,,,,,,,';
        END LOOP;

        v_text = v_text
                 || ',,,,,0,0,,,,,,,,,,,,,,,,,,,,,,,,,,';
        CLOSE c9;
        raise info 'Text: %',v_text;
    END;
$procedure$;

How to pass multiple parameters to a cursor?

Answer 1

With c9, should PostgreSQL use the value of p_plan_version_id as it is in the DECLARE section, or as it is when you OPEN c9? To resolve this ambiguity, you need to pass both parameters when you open the cursor:

DECLARE
   c9 CURSOR (p_product_code varchar, p_pvi numeric) FOR
      SELECT product_code,
             terminal_code,
             quantity
      FROM product_stock pos
      WHERE pos.plan_version_id = p_pvi
        AND pos.product_code    = p_product_code;
BEGIN
   OPEN c9('OIL', p_plan_version_id);
   ...
END;

Here is a complete example to demonstrate that this works:

CREATE FUNCTION sample(upper_limit bigint) RETURNS SETOF bigint
   LANGUAGE plpgsql AS
$$DECLARE
   c CURSOR (l bigint, u bigint) FOR
      SELECT * FROM generate_series(l, u) AS g(c);
   r record;
BEGIN
   OPEN c(1, upper_limit);
   LOOP
      FETCH c INTO r;
      EXIT WHEN NOT FOUND;

      RETURN NEXT r.c;
   END LOOP;
END;$$;

SELECT * FROM sample(10);

 sample 
════════
      1
      2
      3
      4
      5
      6
      7
      8
      9
     10
(10 rows)