Is there any reason why recursive SQL functions are not allowed in PG 7.2?
After all this:
create function foo() returns setof integer as 'select 1'
language 'sql';
create or replace function foo() returns setof integer as
'select foo()'
language 'sql';
Works fine ... (until you call it and run out of stack space!)
It turns out that with the aid of a very simple and efficient recursive
SQL function it is quite easy to devise a key structure for trees that
scales very, very well. Probably better than using hierarchical
("connect by") queries with an appropriate parent foreign key in Oracle,
though I haven't done any serious benchmarking yet.
This is important for the OpenACS project which uses a filesystem
paradigm to organize content in many of its packages.
One of our volunteer hackers figured out an ugly kludge that lets us
define a recursive SQL function in PG 7.1 and it works great, leading to
extremely efficient queries that work on the parents of a given node.
We were thinking we could just declare the function directly in PG 7.2
but instead found we have to resort to a kludge similar to the example
above in order to do it. It's a far nicer kludge than our PG 7.1 hack,
believe me, but we were hoping for a clean define of a recursive function.
SQL functions can return rowsets but recursive ones can't be defined
directly.
Recursive PL/pgSQL functions can be defined directly but they can't
return rowsets.
Sniff...sniff...sniff [:)]
--
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org