Cursor not found-CodePudding

i have created procedure, inside used cursor to update the some data, while calling the procedure it's getting the error.

create or REPLACE PROCEDURE bal_upd(p_id int) as 
$$
    DECLARE rc record;
    ----- cursor
            bal_upd1 CURSOR (p_id int)
            for 
               select * from tbal where custid = p_id;

    begin
               open bal_upd1 (p_id);

              loop
                  FETCH bal_upd1 into rc;
                  exit when not found;
                  update  t_trans set balance = balance   rc.trans;
                  COMMIT;
             end loop;
            close bal_upd1;

    end;

$$ LANGUAGE plpgsql;

call bal_upd(1)

ERROR: cursor "bal_upd1" does not exist CONTEXT: PL/pgSQL function bal_upd(integer) line 12 at FETCH SQL state: 34000

create or REPLACE PROCEDURE bal_upd(p_id int) as 
$$
    DECLARE rc record;
    ----- cursor
            bal_upd1 CURSOR (p_id int)
            for 
               select * from tbal where custid = p_id;

    begin
               open bal_upd1 (p_id);

              loop
                  FETCH bal_upd1 into rc;
                  exit when not found;
                  update  t_trans set balance = balance   rc.trans;
                  COMMIT;
             end loop;
            close bal_upd1;

    end;

$$ LANGUAGE plpgsql;

call bal_upd(1)

ERROR: cursor "bal_upd1" does not exist CONTEXT: PL/pgSQL function bal_upd(integer) line 12 at FETCH SQL state: 34000

CodePudding user response：

You don't need a function or a loop for that:

UPDATE t_trans
SET balance = t_trans.balance   (tbal.trans)
FROM tbal
WHERE t_trans.custid = tbal.custid;

CodePudding user response：

I tried, failed. I just found just use for loop (implicit cursor) is far more simple.

BEGIN;
CREATE temp TABLE tbal (
    custid bigint
    , trans numeric
);
INSERT INTO tbal VALUES (1 , 1);
INSERT INTO tbal VALUES (1 , 2);
CREATE temp TABLE t_trans (
    custid bigint
    , balance numeric
);
INSERT INTO t_trans VALUES (1 , 10);
COMMIT;

CREATE OR REPLACE PROCEDURE bal_upd (bigint)
    AS $func$
DECLARE
    rc record;
BEGIN
    FOR rc IN
    SELECT
        *
    FROM
        tbal
    WHERE
        custid = $1 LOOP
            RAISE NOTICE 'custid: %, trans: % ' , rc.custid , rc.trans;
            UPDATE
                t_trans ta
            SET
                balance = balance   (rc.trans)
            WHERE
                ta.custid = (rc.custid);
        END LOOP;
END;
$func$
LANGUAGE plpgsql;

Then call it. CALL bal_upd(1);