Recursive SQL functions ...

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 ...

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