[postgis-users] Cursor names in a self-nested function

[Kidd, David M]

Appologies, I posted this to the wrong list.


David M. Kidd

Research Associate
Center for Population Biology
Silwood Park Campus
Imperial College London
0207 594 2470



________________________________
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Kidd, David M
Sent: 18 August 2011 13:44
To: PostGIS Users Discussion
Subject: [postgis-users] Cursor names in a self-nested function

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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110818/ff1acf1f/attachment.html>