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@xxxxxxxxxxxxxxxxx W: http://www.magproductions.nl
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
