Re: recursion in plpgsql

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