Re: cursor already in use error

Sim Zacks wrote:
> create or replace function testcursor(thistestid int) returns varchar as
> $$
> declare
>  crs Cursor for select comments from test a join test2 b on
> a.testid=b.testid where a.TestID=thistestid;
>  thiscomment varchar;
>  totalstr varchar;
> begin
>  open crs;
>  fetch crs into thiscomment;
>  totalstr:='';
>  while found loop
>   totalstr:= totalstr || '-' || thiscomment;
>   fetch crs into thiscomment;
>  end loop;

close crs;

>  return totalstr;
> end;
> $$language 'plpgsql';
>
> select name,testcursor(testid) from test; --doesn't work
> select name,testcursor(testid) from test where testid=1; -- works (as does
> testid=2 or 3)

The second query works because you fetch only one record; You don't call
the SP a second time with the cursor still open, while you do with the
first query.

Always close your cursors.

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl