Feature request: allow some way to "return next" a set of values. Usage:
recursive stored procedures to walk a tree. Example: given a table, find all
tables that inherit from it.
Right now, as far as can tell, that requires a little extra effort to merge
the results from different levels of recursion:
create or replace function "DbGetDescendantTables" (oid) returns setof oid
as $$
declare parentid alias for $1;
curid1 oid; curid2 oid;
c1 refcursor;
c2 refcursor;
begin
return next parentid;
open c1 for select inhrelid from pg_inherits where inhparent = parentid;
while 1 loop
fetch c1 into curid1;
if found then
open c2 for select * from "DbGetDescendantTables"(curid1);
while 1 loop
fetch c2 into curid2;
if found then
return next curid2;
else
exit;
end if;
end loop;
close c2;
else
exit;
end if;
end loop;
close c1;
end;
$$ language 'plpgsql';
But if a query result could directly be added to the result set being
accumulated, this would become:
create or replace function "DbGetDescendantTables" (oid) returns setof oid
as $$
declare parentid alias for $1;
curid1 oid;
c1 refcursor;
begin
return next parentid;
open c1 for select inhrelid from pg_inherits where inhparent = parentid;
while 1 loop
fetch c1 into curid1;
if found then
return next (select * from "DbGetDescendantTables"(curid1));
else
exit;
end if;
end loop;
close c1;
end;
$$ language 'plpgsql';
Sure, some of this could be avoid by accumulating and returning an array,
but in my case it's convenient for the procedures to produce result sets.
--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice