'multiple update pgsql using cursor

this code not error but nothing data change please help me. I want to multiple update quantity from some stock table

drop FUNCTION SP_PROSES_STOCK(noresep bigint, p_post_cd varchar);
CREATE or replace FUNCTION SP_PROSES_STOCK(noresep bigint, p_post_cd varchar)
RETURNS void
LANGUAGE plpgsql
as $function$
DECLARE cursorData refcursor;
    v_item_cd varchar;
    v_quantity numeric;

begin

open cursorData FOR
select A.item_cd, A.quantity from trx_medical_resep B
inner join trx_resep_data A on A.medical_resep_seqno = B.medical_resep_seqno
where B.medical_resep_seqno = noresep; 
fetch next from cursorData into v_item_cd,v_quantity;
while (found)
loop
    update inv_pos_item set quantity = quantity - v_quantity 
    where item_cd = v_item_cd and pos_cd = p_post_cd;
end loop;
close cursorData;

END 
$function$
sqlpostgresqlsql-updateplpgsqldatabase-cursor


Solution 1:[1]

I mopdify your function to make it shorter and use FOR. Also I add RAISE NOTICE for debugging. Can you try it:

CREATE or replace FUNCTION SP_PROSES_STOCK(noresep bigint, p_post_cd varchar)
RETURNS void
LANGUAGE plpgsql
as $function$
DECLARE v_cursor record;

BEGIN

   FOR v_cursor IN
       SELECT A.item_cd,
              A.quantity
         FROM trx_medical_resep B
         JOIN trx_resep_data A ON A.medical_resep_seqno = B.medical_resep_seqno
        WHERE B.medical_resep_seqno = noresep
   LOOP
       RAISE NOTICE 'Changes for %', v_curosr; 
       UPDATE inv_pos_item
           SET quantity = quantity - v_cursor.quantity 
         WHERE item_cd = v_cursor.item_cd
           AND pos_cd = p_post_cd;
   END LOOP;
END 
$function$

After debugging you can remove RAISE NOTICE.

Solution 2:[2]

You don't need a loop for this. A single UPDATE statement will be a lot faster:

CREATE or replace FUNCTION SP_PROSES_STOCK(noresep bigint, p_post_cd varchar)
  RETURNS void
as 
$function$
begin

  update inv_pos_item 
     set quantity = quantity - v.quantity 
  from (
    select A.item_cd, A.quantity 
    from trx_medical_resep B
      join trx_resep_data A on A.medical_resep_seqno = B.medical_resep_seqno
    where B.medical_resep_seqno = noresep
  ) v
  where item_cd = v.item_cd and pos_cd = p_post_cd;

END;
$function$
LANGUAGE plpgsql;

Solution 3:[3]

There are scenarios where a cursor is needed. For example lets say you are updating 10 million rows and an exception is thrown at the 6th million record the the update will fail and then it will rollback all of the rows that were updated prior to the failure. If a cursor is used it will be slower but you will have greater control over the process and be able to bypass the error and continue with the posting of the updates. But then again your milage may vary...

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 a_horse_with_no_name
Solution 3 Raul

Related Questions