Re: Cursor names in a self-nested function

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(dot)kidd(at)imperial(dot)ac(dot)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
>
>