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.
David Gauthier <davegauthierpg@gmail.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