PostgreSQL参数化游标多参数传递问题及过程参数引用异常求助

在PostgreSQL存储过程中定义参数化游标时，若游标查询需同时使用存储过程参数和游标自有参数，会出现游标无法读取存储过程参数的问题。示例中无参数游标c2可正常读取存储过程参数p_plan_version_id，但参数化游标c9打开时触发如下错误：

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

提问：如何向PostgreSQL的参数化游标传递多个参数？

错误原因 #

参数化游标内部的变量解析优先级为：游标自身参数 > 存储过程局部变量 > 表列名。示例中c9未声明p_plan_version_id为游标参数，导致PostgreSQL将其误认为是product_stock表的列，从而报错。

解决方法 #

有两种可行方案：

方案1：将存储过程参数纳入游标参数列表

直接把存储过程需要传递给游标的参数声明为游标参数，打开游标时同时传递所有参数。

• 修改游标c9的定义，添加p_plan_version_id作为游标参数：

  C9 CURSOR (p_plan_version_id NUMERIC, 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;
  

• 打开游标时同时传递两个参数：

  OPEN c9(p_plan_version_id, 'OIL');
  

方案2：使用局部变量中转存储过程参数

在存储过程内部将参数赋值给局部变量，游标查询中使用该局部变量（局部变量优先级高于表列）。

• 在DECLARE块添加局部变量：

  v_plan_version_id NUMERIC := p_plan_version_id;
  

• 修改游标c9的查询条件：

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

修改后的完整代码（方案1） #

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 pq.product_code = ep.product_code
            GROUP BY product_code;

        -- 声明游标需要的所有参数
        C9 CURSOR (p_plan_version_id NUMERIC, 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;
        LOOP
            FETCH c2 INTO j;
            EXIT WHEN NOT found;

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

        CLOSE c2;

        -- 传递两个参数打开游标
        OPEN c9(p_plan_version_id, '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$;

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