Обсуждение: recursion in plpgsql
Hi:
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.
I know I did this sort of thing in the past, but I can't remember if I used cursors or some other construct to traverse the hierarchy.
Here's the code that's failing...
========================================================
create or replace function spk_fix_areas(parent_id int)
returns text as $$
declare
par_area text;
child int;
child_node_curr cursor for select id from spk_ver_node where parent = parent_id;
area_id int;
area_area text;
begin
select area into par_area from spk_ver_task_area where id = parent_id;
open child_node_curr;
loop
fetch child_node_curr into child;
exit when not found;
raise notice 'child: %',child;
select id,area into area_id,area_area from spk_ver_task_area where id = child and area = par_area;
continue when found;
raise notice 'attempting insert child = %, area = %',child,par_area;
insert into spk_ver_task_area (id,area) values (child,par_area);
select spk_fix_areas(child);
end loop;
return('done');
end;
$$ language plpgsql;
===============================================
Thanks for any help !
On Tue, Nov 6, 2018 at 2:54 PM David Gauthier <davegauthierpg@gmail.com> wrote:
Hi: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.I know I did this sort of thing in the past, but I can't remember if I used cursors or some other construct to traverse the hierarchy.
Recursive common-table-expression queries would be the typical way. Perhaps that's what you used before
Cheers,
Steve
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
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@sss.pgh.pa.us> wrote:
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
>>>>> "David" == David Gauthier <davegauthierpg@gmail.com> writes:
David> Tom: I seem to remember (but am probably wrong) that cursors
David> were locally scoped (or could be made so). This was several
David> years ago with an earlier v8 version. Was that sort of thing
David> around back then ?
There are two distinct objects here being called "cursor": one is the
plpgsql variable, which is locally scoped, and the other is the actual
open portal, which must have a unique name within the session.
By default, plpgsql explicit bound cursors (but not plain "refcursor"
variables) take their portal name from the plpgsql variable name, and
hence don't work recursively by default. This is a convenience so that
code outside the function can use the same name to refer to the open
portal.
However, plpgsql cursor variables (whether declared bound or unbound)
can be assigned a text value or NULL _before_ being opened, and if so,
that value will be used for the portal name, or if NULL, a name of
"<unnamed portal N>" will be uniquely generated. (_After_ the open, the
variable's text value is the actually assigned portal name.) Unbound
refcursor variables default to NULL, so they are assigned unique portal
names on opening.
So in your example, adding
child_node_curr := NULL;
immediately before the OPEN statement should be sufficient.
--
Andrew (irc:RhodiumToad)
Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> [ nice summary ]
Should we try to improve the docs in this area?
regards, tom lane