From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Kidd, David M <d.kidd@imperial.ac.uk>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Cursor names in a self-nested function
Date: Thu, 18 Aug 2011 12:15:33 -0400
Message-ID: <5483.1313684133@sss.pgh.pa.us> (raw)
In-Reply-To: <CAFj8pRA363gGDweH4Qgur0c7QWZwiYe2V1xMNbH+GiGZxLG8Sw@mail.gmail.com>
References: <92F7EFF74482D04B8548581D147F6AE70403DF@icexch-m3.ic.ac.uk>
<CAFj8pRA363gGDweH4Qgur0c7QWZwiYe2V1xMNbH+GiGZxLG8Sw@mail.gmail.com>
Pavel Stehule <pavel.stehule@gmail.com> 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
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: tgl@sss.pgh.pa.us, pavel.stehule@gmail.com, d.kidd@imperial.ac.uk
Subject: Re: Cursor names in a self-nested function
In-Reply-To: <5483.1313684133@sss.pgh.pa.us>
* 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