CODEWITHSUNDEEP
postgresqlcursorplpgsql
k.xf
k.xf

Reputation: 399

Some error about cursor in Pl/pgsql

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.

Upvotes: 0

Views: 276

Answers (1)

k.xf
k.xf

Reputation: 399

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

  1. at for row in cur LOOP ,
  2. 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.

Upvotes: 1

Related Questions