Re: [SQL] Cursor names in a self-nested function

2011-08-18 Thread Pavel Stehule 
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 d.k...@imperial.ac.uk:
 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



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Cursor names in a self-nested function

2011-08-18 Thread Tom Lane 
Pavel Stehule pavel.steh...@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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql