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;