To: Pavel Stehule <pavel.stehule@gmail.com>
cc: "Kidd, David M" <d.kidd@imperial.ac.uk>,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: Cursor names in a self-nested function 
In-reply-to: 
 <CAFj8pRA363gGDweH4Qgur0c7QWZwiYe2V1xMNbH+GiGZxLG8Sw@mail.gmail.com>
References: <92F7EFF74482D04B8548581D147F6AE70403DF@icexch-m3.ic.ac.uk>
	<CAFj8pRA363gGDweH4Qgur0c7QWZwiYe2V1xMNbH+GiGZxLG8Sw@mail.gmail.com>
Comments: In-reply-to Pavel Stehule <pavel.stehule@gmail.com>
	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 <tgl@sss.pgh.pa.us>

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