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