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


Mon, 17 Dec 2001 10:30:31 -0800
Don Baccus <dhogaza@pacifier.com>
Recursive SQL functions ...