Hello
you can use a refcursor type
http://developer.postgresql.org/pgdocs/postgres/plpgsql-cursors.html
Regards
Pavel Stehule
2011/8/18 Kidd, David M <d.kidd@imperial.ac.uk>:
> Hi,
>
> I am trying to write a function that contains a cursor and iteratively calls
> itself.
>
> It is along the lines of,
>
> CREATE FUNCTON test(id integer) RETURNS TEXT AS
> $BODY$
> DECLARE
> mycursor CURSOR FOR SELECT * FROM myfunction(id);
> newid INTEGER;
> out = TEXT;
> BEGIN
> out := '';
> OPEN mycursor;
> LOOP
> FETCH my_cursor INTO newid;
> out := out || test (newid);
> END LOOP;
> RETURN out;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
>
> This returns an ERROR stating that "mycursor" is already in use.
>
> I understand this occurs because cursor names must be unique across, as well
> as within, functions.
>
> So, my question is whether there is a way I can dynamically declare a cursor
> name, for example by appending a incremental number or guid to make the name
> unique?
> Just trying to concatenate two passed arguments in the DECLARE statement
> unsurprisingly fails.
>
> Any other solutions are of cause welcome.
>
> Many thanks,
>
> - David
>
>
>
> David M. Kidd
>
> Research Associate
> Center for Population Biology
> Silwood Park Campus
> Imperial College London
> 0207 594 2470
>
>