Some error about cursor in Pl/pgsql

Question

I know the right way to insert value from a table:
insert into city (pop) select pop+2 from city
I just want to know how cursor works in Pl/pgsql .
I wish to use cursor in loop to insert some value:

create or replace function test(  ) returns void as 
$$
declare
cur cursor for select pop+2 from city order by pop;
b int;
row record;
begin
for row in cur
LOOP 
fetch cur into b;
insert into city(pop) select b;
end loop;
end;
$$ language plpgsql

However, when I type select test() and the result table is:

enter image description here

It's very strange that only two rows are inserted. So I want to know what lead to this result?

Update my question in 04/05/2016:
I revise the function like this:

create or replace function test(  ) returns void as 
$$
declare
cur cursor for select * from city order by pop;
b record;
begin
for b in cur
LOOP 
insert into city(pop) select b.pop+2;
RAISE NOTICE ' % IS INSERTED' , b;
end loop;
end;
$$ language plpgsql

Then I get the correct result:
enter image description here

But I still wonder why in the first function, only two rows are inserted.

Why are you using a (slow and inefficient) cursor for this in the first place? You can do this much more efficiently using a single statement: insert into city (pop) select pop+2 from city;. — user330315
– user330315, Commented May 3, 2016 at 14:38
Just a guess: there are two fetches in the your loop: first, implicit, at for row in cur LOOP and second, explicit, at fetch cur into b;. So at each iteration you are doing actually two fetches. — Abelisto
– Abelisto, Commented May 3, 2016 at 15:10

k.xf · Accepted Answer · 2016-05-05 08:33:21Z

1

I finally figure out that why the result is wrong, just like Abelisto's comment . I did two fetchers in loop at each step:

at for row in cur LOOP ,
at fetch cur into b

So the first row where pop=500 and the third row where pop =1000 have already been fetched in for loop, and it can't be fetched by b.

edited May 5, 2016 at 8:33

answered May 4, 2016 at 2:11

k.xf

4091 gold badge6 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

Some error about cursor in Pl/pgsql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related