From: Kidd, David M <d.kidd@imperial.ac.uk>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Cursor names in a self-nested function
Date: Thu, 18 Aug 2011 14:53:18 +0000
Message-ID: <92F7EFF74482D04B8548581D147F6AE70403DF@icexch-m3.ic.ac.uk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: d.kidd@imperial.ac.uk
Subject: Re: Cursor names in a self-nested function
In-Reply-To: <92F7EFF74482D04B8548581D147F6AE70403DF@icexch-m3.ic.ac.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox