postgresql cursor "fetch into" loop doesn't return any data

Question

Any idea what I'm doing wrong here?

CREATE OR REPLACE FUNCTION update_prices_in_ord1( ) RETURNS void  AS $$
DECLARE
  cur CURSOR for select ord2.ord1_id, sum(ord2.price*ord2.qty) as totprice from ord2 group by ord1_id;
  --tmpid int;
  --tmpid ord2.ord1_id%TYPE;
  --tmpprice float;
  mydata RECORD;
BEGIN
  open cur;
  loop
    --fetch cur into tmpid,tmpprice;
    fetch cur into mydata;
    --raise notice 'price=%, id=%', tmpprice, tmpid;
    raise notice 'price=%, id=%', mydata.totprice, mydata.ord1_id;
    update ord1 set price=mydata.totprice where id=mydata.ord1_id;
  end loop;
  close cur;
END;
$$ LANGUAGE plpgsql;

As you can see I've tried a few options (in comments) but no luck. All I get is infinite nulls:

NOTICE:  price=<NULL>, id=<NULL>

If I run the sql of cursor alone it runs fine:

testdb=# select ord2.ord1_id, sum(ord2.price*ord2.qty) as totprice from ord2 group by ord1_id;
 ord1_id | totprice 
---------+----------
      14 |       10
      27 |     42.5
      17 |     57.5
      28 |       43
      15 |      142
...

All I want to do is to update the ord1.price field, based on above totalprice, for the matching ord1.id.

Thanks a lot!

Nick Barnes · Accepted Answer · 2014-10-05 10:53:44Z

1

You've written an unconditional loop. Since there's no exit or return statement, it will never stop.

A for loop takes care of this for you. On top of that, it will open, fetch from, and close the cursor automatically:

BEGIN
  for mydata in cur loop
    raise notice 'price=%, id=%', mydata.totprice, mydata.ord1_id;
    update ord1 set price=mydata.totprice where id=mydata.ord1_id;
  end loop;
END;

You don't need a loop to do this, though; you should be able to write this update in plain SQL:

update ord1
set price = total.totprice
from (select ord1_id, sum(price*qty) as totprice from ord2 group by ord1_id) total
where ord1.id = total.ord1_id

edited Oct 5, 2014 at 10:53

answered Oct 5, 2014 at 3:02

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ilias Over a year ago

Right, actually it's: update ord1 ... where ord1.id = total.ord1_id; Basically my original code was also working, as long as I added "exit WHEN NOT FOUND;" after fetch. (Otherwise the data would still be updated, but the function would loop for ever - that's why I was seeing "infinite nulls" - if I had looked for the first raise outputs, I would have seen that it wouldn't be "nulls" and that it was actually "working".) Thanks a lot!

Collectives™ on Stack Overflow

postgresql cursor "fetch into" loop doesn't return any data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related