Re: recursion in plpgsql

Thanks for the replies !

Steve: I don't remember using a recursive query like that, but it certainly
does look interesting.

Tom: I seem to remember (but am probably wrong) that cursors were locally
scoped (or could be made so). This was several years ago with an earlier
v8 version. Was that sort of thing around back then ?
Perhaps I stuffed the results in an array or temp table and then fed out of
that in recursive calls. I remember having to stuff arrays in oracle 8 WAY
back in the day, and then read out of that for recursive calls. But I also
seem to remember being delighted with plpsql because that wasn't necessary
anymore.

On Tue, Nov 6, 2018 at 7:29 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Gauthier <davegauthierpg(at)gmail(dot)com> writes:
> > I'm trying/failing to write a recursive plpgsql function where the
> function
> > tries to operate on a hierary of records in a reflexive table.
> > parent-child-grandchild type of recursion.
> > I tried with a cursor, but got a "cursor already in use" error. So that
> > looks like scoping.
>
> IIRC, the "portal" underlying a plpgsql cursor just gets the same name
> as the cursor variable by default, so you'll get portal-name conflicts
> with the coding style you show here.
>
> It's possible to avoid that by ensuring that each cursor gets a different
> portal name. I'm too lazy to check the details right now, but at the
> very least there's a way to do it by declaring the variable as "refcursor"
> and assigning it a different name at each nesting depth. There might be
> some more elegant solution, too.
>
> regards, tom lane
>