<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="GENERATOR" content="MSHTML 8.00.6001.19120">
</head>
<body>
<div dir="ltr" align="left"><span class="880122213-18082011"><font color="#0000ff" size="2" face="Arial">Appologies, I posted this to the wrong list.</font></span></div>
<div> </div>
<!-- Converted from text/plain format -->
<p align="left"><font size="2">David M. Kidd</font></p>
<p align="left"><font size="2">Research Associate<br>
Center for Population Biology<br>
Silwood Park Campus<br>
Imperial College London<br>
0207 594 2470</font></p>
<div> </div>
<br>
<div dir="ltr" lang="en-us" class="OutlookMessageHeader" align="left">
<hr tabindex="-1">
<font size="2" face="Tahoma"><b>From:</b> postgis-users-bounces@postgis.refractions.net [mailto:postgis-users-bounces@postgis.refractions.net]
<b>On Behalf Of </b>Kidd, David M<br>
<b>Sent:</b> 18 August 2011 13:44<br>
<b>To:</b> PostGIS Users Discussion<br>
<b>Subject:</b> [postgis-users] Cursor names in a self-nested function<br>
</font><br>
</div>
<div></div>
<div><font size="2" face="Arial"><span class="736512212-18082011">Hi,</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"></span></font> </div>
<div><font size="2" face="Arial"><span class="736512212-18082011">I am trying to write a function that contains a cursor and iteratively calls itself.
</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"></span></font> </div>
<div><font size="2" face="Arial"><span class="736512212-18082011">It is along the lines of,</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"></span></font> </div>
<div><font size="2" face="Arial"><span class="736512212-18082011">CREATE FUNCTON test(id integer) RETURNS TEXT AS</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011">$BODY$</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011">DECLARE</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"> mycursor CURSOR FOR SELECT * FROM myfunction(id);</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"> newid INTEGER;</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"> out = TEXT;</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011">BEGIN</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"> out := '';</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"> OPEN mycursor;</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"> LOOP</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"> FETCH my_cursor INTO newid;</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"> out := out || test (newid);</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"> END LOOP;</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"> RETURN out;</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011">END;</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"> $BODY$</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011">LANGUAGE 'plpgsql' VOLATILE</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"></span></font> </div>
<div><font size="2" face="Arial"><span class="736512212-18082011">This returns an ERROR stating that "mycursor" is already in use.</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"></span></font> </div>
<div><font size="2" face="Arial"><span class="736512212-18082011">I understand this occurs because cursor names must be unique across, as well as within, functions.
</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"></span></font> </div>
<div><font size="2" face="Arial"><span class="736512212-18082011">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?</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011">Just trying to concatenate two passed arguments in the DECLARE statement unsurprisingly fails.
</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"></span></font> </div>
<div><font size="2" face="Arial"><span class="736512212-18082011">Any other solutions are of cause welcome.</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"></span></font> </div>
<div><font size="2" face="Arial"><span class="736512212-18082011">Many thanks,</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"></span></font> </div>
<div><font size="2" face="Arial"><span class="736512212-18082011"> - David</span></font></div>
<div><font size="2" face="Arial"><span class="736512212-18082011"></span></font> </div>
<!-- Converted from text/plain format -->
<p align="left"><font size="2">David M. Kidd</font></p>
<p align="left"><font size="2">Research Associate<br>
Center for Population Biology<br>
Silwood Park Campus<br>
Imperial College London<br>
0207 594 2470</font></p>
<div> </div>
</body>
</html>