From d.kidd@imperial.ac.uk Thu Aug 18 15:32:54 2011 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_-- From pavel.stehule@gmail.com Thu Aug 18 15:36:55 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 10CD2B5DBEA for ; Thu, 18 Aug 2011 12:36:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 75727-03 for ; Thu, 18 Aug 2011 15:36:48 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vx0-f174.google.com (mail-vx0-f174.google.com [209.85.220.174]) by mail.postgresql.org (Postfix) with ESMTP id DBDC1B5D80A for ; Thu, 18 Aug 2011 12:36:47 -0300 (ADT) Received: by vxi9 with SMTP id 9so1816229vxi.19 for ; Thu, 18 Aug 2011 08:36:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; bh=pe3URUAQj87ytRh2AiSTR9NavtgQ47MLHS7sVW3MMWo=; b=Xzsp3NusIJ97AOrlCPye9mJrar/0jXhT3ik/fTYXELH7x0Vd2Tv4zbQIMSyKQ1Gboz QEKuVTl4Lk4yP9T1ij6lvYkhuQ4fJU7hy2mkmbf5O0+e8NE3qaiIDOTcB0iLvwe48A/M avgazhDg+eq4RORn1+Repn4rOCMIFiNotqf6U= Received: by 10.220.202.4 with SMTP id fc4mr269489vcb.99.1313681807111; Thu, 18 Aug 2011 08:36:47 -0700 (PDT) MIME-Version: 1.0 Received: by 10.220.93.16 with HTTP; Thu, 18 Aug 2011 08:36:07 -0700 (PDT) In-Reply-To: <92F7EFF74482D04B8548581D147F6AE70403DF@icexch-m3.ic.ac.uk> References: <92F7EFF74482D04B8548581D147F6AE70403DF@icexch-m3.ic.ac.uk> From: Pavel Stehule Date: Thu, 18 Aug 2011 17:36:07 +0200 Message-ID: Subject: Re: Cursor names in a self-nested function To: "Kidd, David M" Cc: "pgsql-sql@postgresql.org" Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201108/73 X-Sequence-Number: 35741 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 : > Hi, > > I am trying to write a function that contains a cursor=C2=A0and iterative= ly calls > itself. > > It is=C2=A0along the lines of, > > CREATE FUNCTON test(id integer) RETURNS TEXT AS > $BODY$ > DECLARE > =C2=A0 mycursor CURSOR FOR SELECT * FROM myfunction(id); > =C2=A0 newid INTEGER; > =C2=A0 out =3D TEXT; > BEGIN > =C2=A0 out :=3D ''; > =C2=A0 OPEN mycursor; > =C2=A0 LOOP > =C2=A0=C2=A0=C2=A0 FETCH my_cursor INTO newid; > =C2=A0=C2=A0=C2=A0 out :=3D out || test (newid); > =C2=A0 END LOOP; > =C2=A0 RETURN out; > END; > =C2=A0$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 w= ell > as within, functions. > > So, my question is whether there is a way I can dynamically declare a cur= sor > 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 > unsurprisingly fails. > > Any other solutions are of cause welcome. > > Many thanks, > > =C2=A0- David > > > > David M. Kidd > > Research Associate > Center for Population Biology > Silwood Park Campus > Imperial College London > 0207 594 2470 > > From tgl@sss.pgh.pa.us Thu Aug 18 16:15:44 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 9606EB5DBFA for ; Thu, 18 Aug 2011 13:15:44 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 62482-06 for ; Thu, 18 Aug 2011 16:15:37 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id E0119B5DBF9 for ; Thu, 18 Aug 2011 13:15:36 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p7IGFXuQ005484; Thu, 18 Aug 2011 12:15:33 -0400 (EDT) To: Pavel Stehule cc: "Kidd, David M" , "pgsql-sql@postgresql.org" Subject: Re: Cursor names in a self-nested function In-reply-to: References: <92F7EFF74482D04B8548581D147F6AE70403DF@icexch-m3.ic.ac.uk> Comments: In-reply-to Pavel Stehule message dated "Thu, 18 Aug 2011 17:36:07 +0200" Date: Thu, 18 Aug 2011 12:15:33 -0400 Message-ID: <5483.1313684133@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-3.037 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-1.137 X-Spam-Level: X-Archive-Number: 201108/74 X-Sequence-Number: 35742 Pavel Stehule writes: > you can use a refcursor type > http://developer.postgresql.org/pgdocs/postgres/plpgsql-cursors.html It would suffice to explicitly set mycursor to null before doing the OPEN, thus instructing the system to assign a unique cursor name. CREATE FUNCTION test(id integer) RETURNS TEXT AS $BODY$ DECLARE mycursor CURSOR FOR SELECT * FROM int4_tbl WHERE f1 > id; newid INTEGER; out TEXT; BEGIN out := id::text || ' '; mycursor := null; OPEN mycursor; raise notice 'mycursor = %', mycursor; -- debug LOOP FETCH mycursor INTO newid; EXIT WHEN newid IS NULL; out := out || test (newid); END LOOP; RETURN out; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; regards, tom lane