Re: hierarchy select question? - Mailing list pgsql-general

Joe Selko's book SQL for Smarties (Morgan Kaufmann, 1995)
Chapter 26 covers the topic querying and updating trees.
It describes several different approaches and some vendor
specific extensions for handling tree data.

It mentions CJ Date's EXPLODE(<table name>) operator that
would convert a table into another table with four columns:
-- the level number
-- the current node
-- the subordinate node
-- the sequence number

Tree structured data is so pervasive (discussion forums, bill of
materials, organization charts, software configuration,  project
management, file directories, etc) one would think there ought
to be a lot of interest in making this easy to do in Postgres.

Is anyone interested to fund some development in this area?
This is likely to be a pretty big task, so if several interested
parties could pool their resources, it would make it much more
affordable.

Cheers,
Craig

Chris Bitmead wrote:

> Hi,
>
> Originally postgres had a "recursive select" to handle cases like this.
> Some syntax like...
> retrieve* (notice the "*") which meant keep executing until you can't
> anymore, and using an
> appropriate where clause it would decend tree-like structures.
>
> This feature disappeared somewhere along the way. There is I think a
> similar concept in SQL
> or SQL-99 which needs to be (re)implemented sometime, but I don't think
> there's an easy way
> right now. There was some talk on the hackers list recently about how to
> implement parent
> child comments in discussion forums, but I'm not sure if a nice solution
> came along. Some
> people seemed confident that there was a way.
>
> database@gurubase.com wrote:
> >
> > Dear all,
> >
> > I would like to define threads in message system for replies to message but if
> > I define too many level, I am afraid I have problem in the select...
> >
> > Say, I have define 3 levels:
> >
> >     1
> >    / \
> >   2   3
> >  /\    \
> > 4   5   6
> >
> > It means message 2 is a reply to 1.
> >                  4 is a further follow-up of 2...etc
> >
> > In table format, I would present it with
> >
> > Table A
> > ParentId  ChildId
> > 1         2
> > 1         3
> > 2         4
> > 2         5
> > 3         6
> >
> > I think I can issue the command to join table A 3 times to give the following
> > result
> >
> >       1st    2nd     3rd
> > Row1    1      2       4
> > Row2    1      2       5
> > Row3    1      3       6
> >
> > But can I show
> > a. which level each node belongs
> > b. no. of child nodes it has
> > altogether in one "select" sql
> >
> > and if the level exceeds 3, how can I do it?
> >
> > Many thanks.....
> >
> > Best regards,
> > Boris


====================================
Craig Johannsen
Critical Path Consulting, Inc.
604-762-1514
http://members.home.net/cjohan/cpath
====================================