<!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><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>