pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Cursor names in a self-nested function
3+ messages / 3 participants
[nested] [flat]

* Cursor names in a self-nested function
@ 2011-08-18 14:53  Kidd, David M <d.kidd@imperial.ac.uk>
  0 siblings, 1 reply; 3+ messages in thread

From: Kidd, David M @ 2011-08-18 14:53 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Cursor names in a self-nested function
@ 2011-08-18 15:36  Pavel Stehule <pavel.stehule@gmail.com>
  parent: Kidd, David M <d.kidd@imperial.ac.uk>
  0 siblings, 1 reply; 3+ messages in thread

From: Pavel Stehule @ 2011-08-18 15:36 UTC (permalink / raw)
  To: Kidd, David M <d.kidd@imperial.ac.uk>; +Cc: pgsql-sql

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.kidd@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
>
>



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Cursor names in a self-nested function
@ 2011-08-18 16:15  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Pavel Stehule <pavel.stehule@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2011-08-18 16:15 UTC (permalink / raw)
  To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Kidd, David M <d.kidd@imperial.ac.uk>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2011-08-18 16:15 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-18 14:53 Cursor names in a self-nested function Kidd, David M <d.kidd@imperial.ac.uk>
2011-08-18 15:36 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-08-18 16:15   ` Tom Lane <tgl@sss.pgh.pa.us>

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