[Fwd: SQL3 recursive unions] - Mailing list pgsql-general

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@yellowbank.comHave you looked at the recursive union syntax in SQL3? It seems DB2 is
the only database that support it right now, but it's an interesting
syntax that allows for all kinds of explosions.

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

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
)
select distinct part, subpart, quantity
  from rpl
  order by part, subpart, quantity

If this looks confusing -- it's not so intuitive, and introduces two new terms, the temporary-table "with" syntax and
"unionall" -- the documentation explains this query thus: 

<quote>
The above query includes a common table expression, identified by the name RPL, that expresses the recursive part of
thisquery. It illustrates the basic elements of a recursive common table expression.  

The first operand (fullselect) of the UNION, referred to as the initialization fullselect, gets the direct children of
part'01'. The FROM clause of this fullselect refers to the source table and will never refer to itself (RPL in this
case).The result of this first fullselect goes into the common table expression RPL (Recursive PARTLIST). As in this
example,the UNION must always be a UNION ALL.  

The second operand (fullselect) of the UNION uses RPL to compute subparts of subparts by having the FROM clause refer
tothe common table expression RPL and the source table with a join of a part from the source table (child) to a subpart
ofthe current result contained in RPL (parent). The result goes back to RPL again. The second operand of UNION is then
usedrepeatedly until no more children exist.  

The SELECT DISTINCT in the main fullselect of this query ensures the same part/subpart is not listed more than once.
</quote>

--A.

"Jon Udell" <udell@monad.net> wrote in message news:393531
01.C8FC995@monad.net...
> > I'm not talking about complicated, unusual, or atypical data
structures here.
> > I'm talking about A GODDAMN TREE.  Why can't SQL do this!!!
> >
> > As others have pointed out - IT CAN!  It's been talked about for
years!
> > It's just not a standard part of a typical SQL implementation.
> > And I'm just saying it should be.
>
> What would the syntax look like, in your view? Things mentioned in
this
> thread have included the technique described in Celko Chap 26
(standard,
> but ungainly) and Oracle's CONNECT BY (nonstandard, but concise).
>
> What kind of tree operations would you like to be able to express in
> SQL, and how would you like to express them?
>
> --
> Jon Udell | <http://udell.roninhouse.com/> | 603-355-8980