Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 13243B5DBF8 for ; Thu, 18 Aug 2011 12:32:54 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 62194-06 for ; Thu, 18 Aug 2011 15:32:47 +0000 (UTC) X-Greylist: delayed 00:38:36.497036 by SQLgrey-1.7.6 Received: from smtp2.cc.ic.ac.uk (smtp2.cc.ic.ac.uk [155.198.5.156]) by mail.postgresql.org (Postfix) with ESMTP id 63AB4B5D80A for ; Thu, 18 Aug 2011 12:32:47 -0300 (ADT) Received: from icexch-h2.ic.ac.uk ([155.198.59.12]) by smtp2.cc.ic.ac.uk with esmtp (Exim 4.72) (envelope-from ) id 1Qu3z0-0002zq-35 for pgsql-sql@postgresql.org; Thu, 18 Aug 2011 15:54:10 +0100 Received: from icexch-m3.ic.ac.uk ([fe80::bd02:fc07:49f4:cded]) by icexch-h2.ic.ac.uk ([fe80::c06e:5d25:f2dc:fe8%10]) with mapi id 14.01.0323.000; Thu, 18 Aug 2011 15:53:19 +0100 From: "Kidd, David M" To: "pgsql-sql@postgresql.org" Subject: Cursor names in a self-nested function Thread-Topic: Cursor names in a self-nested function Thread-Index: AcxdtpCosVY5SFQdSgGYzz3Gy5fZsw== Date: Thu, 18 Aug 2011 14:53:18 +0000 Message-ID: <92F7EFF74482D04B8548581D147F6AE70403DF@icexch-m3.ic.ac.uk> Accept-Language: en-GB, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [129.31.4.118] Content-Type: multipart/alternative; boundary="_000_92F7EFF74482D04B8548581D147F6AE70403DFicexchm3icacuk_" MIME-Version: 1.0 X-IC-MsgID: 1Qu3z0-0002zq-35 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201108/72 X-Sequence-Number: 35740 --_000_92F7EFF74482D04B8548581D147F6AE70403DFicexchm3icacuk_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, I am trying to write a function that contains a cursor and iteratively call= s 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 =3D TEXT; BEGIN out :=3D ''; OPEN mycursor; LOOP FETCH my_cursor INTO newid; out :=3D 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 wel= l as within, functions. So, my question is whether there is a way I can dynamically declare a curso= r name, for example by appending a incremental number or guid to make the n= ame unique? Just trying to concatenate two passed arguments in the DECLARE statement un= surprisingly 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 --_000_92F7EFF74482D04B8548581D147F6AE70403DFicexchm3icacuk_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,=

I a= m trying to write a function that contains a cursor and iteratively ca= lls itself.

It = is along the lines of,

CRE= ATE FUNCTON test(id integer) RETURNS TEXT AS

$BO= DY$

DEC= LARE

&nb= sp; mycursor CURSOR FOR SELECT * FROM myfunction(id);

&nb= sp; newid INTEGER;

&nb= sp; out =3D TEXT;

BEG= IN

&nb= sp; out :=3D '';

&nb= sp; OPEN mycursor;

&nb= sp; LOOP

&nb= sp; FETCH my_cursor INTO newid;

&nb= sp; out :=3D out || test (newid);

&nb= sp; END LOOP;

&nb= sp; RETURN out;

END= ;

&nb= sp;$BODY$

LAN= GUAGE 'plpgsql' VOLATILE

Thi= s returns an ERROR stating that "mycursor" is already in use.

I u= nderstand this occurs because cursor names must be unique across, as well a= s within, functions.

So,= my question is whether there is a way I can dynamically declare a cursor n= ame, for example by appending a incremental number or guid to make the name= unique?

Jus= t trying to concatenate two passed arguments in the DECLARE statement unsur= prisingly fails.

Any= other solutions are of cause welcome.

Man= y thanks,

&nb= sp;- David

David M. Kidd

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

--_000_92F7EFF74482D04B8548581D147F6AE70403DFicexchm3icacuk_--