Re: [Fwd: SQL3 recursive unions]

PostgreSQL's TODO list (http://www.postgresql.org/docs/todo.html) makes
reference to implementing SQL3 recursive queries. Where does this task
fall in the overall priority of things? I hate to just whine without
offering to help, but I think this would be a rather big bite to chew.
(Actually, I would be happy to help, if anyone had any suggestions about
what I might be able to do)

The attached message includes an example of recursive SQL syntax from
IBM's DB2.

I keep thinking that must be some way to do something similar using
PostgreSQL in it's current state, but if so, elegant solutions elude
me. Drives me nuts.

I want to do an exploded parts list. I want to create a tape archive
database that stores directory entries efficiently. Threaded
discussions. Business hierarchies. Etc. Sometimes, fixed depth
hierarchies just don't cut it.

I thought, once, that DB master Joe Celko had described an elegant
solution using standard SQL (http://www.dbmsmag.com/9603d06.html). But
you don't have to think too hard before you realize that using this
method, insertions and deletions could easily require updates to every
row in your table.

Oracle's CONNECT BY, LEVELS, & START AT are nice. But I've heard said
they don't provide the same degree of flexibility that recursive queries
do, although Oracle's methodology may be faster...??

Am I alone in the universe? If I can't get the feature I want, I guess
I'll settle for some sympathy...

Woe is me.

________________________
Ron Peterson
rpeterson(at)yellowbank(dot)com

On Thu, Jun 15, 2000 at 03:58:40PM -0400, Ron Peterson wrote:
> PostgreSQL's TODO list (http://www.postgresql.org/docs/todo.html) makes
> reference to implementing SQL3 recursive queries. Where does this task
> fall in the overall priority of things? I hate to just whine without

I want to implement this for some years now. But I haven't found the time to
even start yet.

> offering to help, but I think this would be a rather big bite to chew.
> (Actually, I would be happy to help, if anyone had any suggestions about
> what I might be able to do)

So at least we are two who are interested in this.

> Here's an example from DB2's documentation, cleaned up a bit:

Is this SQL3 syntax?

> with rpl (part, subpart, quantity) as
> (
> select root.part, root.subpart, root.quantity
> from partlist root
> where root.part = '01'
> union all
> select child.part, child.subpart, child.quantity
> from rpl parent, partlist child
> where parent.subpart = child.part
> )

Hmm, this doesn't look correct. What if a subpart is used by different
parts? I think the second query needs an aggregate.

But the above query certainly does solve the ancestor problem.

Does anyone have an idea how DB2 executes such a statement?

Michael
--
Michael Meskes
Michael(at)Fam-Meskes(dot)De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!