Question 90585 in PostgreSQL Question posted in
The official documentation can be found here.

Cursor not found – Postgresql

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

Tags:

2

Answers


  1. 0

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

    Login or Signup to reply.
  2. 0

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

    UPDATE t_trans
SET balance = t_trans.balance + t.trans
FROM (SELECT sum(trans) AS trans
      FROM tbal
      GROUP BY custid) AS t
WHERE t_trans.custid = t.custid;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search