Postgres 游标未采用多个参数-解网

问：

我创建了一个过程，并在该过程中创建了一个参数化游标，我将参数传递给它。Cursor 查询有两个参数：一个是过程的参数，另一个是游标的参数。

当我使用游标的参数打开游标时，它没有采用过程参数的值。当我创建一个没有参数的游标时，它正在接受过程的参数。

请参阅以下示例：打开良好并将数据插入，但是当我尝试打开时，它给了我错误c2v_textc9

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

如何将多个参数传递给一个游标？

PostgreSQL 游标 plpgsql

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;

下面是一个完整的示例来证明这可行：

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)

Postgres 游标未采用多个参数

Postgres cursor is not taking multiple parameters

评论

评论