Re: pl-pgsql, recursion and cursor contexting

In all fairness, I believe in Oracle I was declaring explicit cursors
(by name) and recursive calls would fail outright with complaints that
the cursor was already open.  There was (to the best of my knowledge)
nothing like the "for <select...> in loop..." construct in Oracle's
PLSQL language.

-dave

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, September 29, 2008 10:28 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pl-pgsql, recursion and cursor contexting

"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> I'm in the business of writting recursive PL-Pgsql functions.  I need
to
> know what happens to the data stream from a select cursor inside of
> which the recursive call is made.  For example....

Nothing, unless you use explicitly-named cursors and force a cursor name
conflict.  A for-loop's internal cursor always gets a name chosen to be
distinct from every other existing cursor, so there's no conflict.

> This comes up witht he right answer.  IOW, making the recursive call
> from within the "for rec in..." loop doesn't seem to destroy the data
> streams from earlier calls.  I just need to make sure that this will
> always be the case and that getting the correct result in this example
> is not just an artifact of it's simplicity.  I know, for example, this
> was a no-no in Oracle.

Wow, are they really that broken?

            regards, tom lane