From: | Don Baccus <dhogaza(at)pacifier(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | recursive SQL functions |
Date: | 2001-12-17 19:49:36 |
Message-ID: | 3C1E4C50.3070003@pacifier.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-12-17 20:35:21 | Re: PG 7.2b4 bug? |
Previous Message | Don Baccus | 2001-12-17 19:47:40 | PG 7.2b4 bug? |