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(at)magproductions(dot)nl
W: http://www.magproductions.nl