pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGet A Tree from a table
3+ messages / 3 participants
[nested] [flat]
* Get A Tree from a table
@ 2002-10-06 05:37 Scott Yaung <gzscott2001@yahoo.ca>
2002-10-07 16:40 ` Re: Get A Tree from a table Josh Berkus <josh@agliodbs.com>
2002-10-07 16:54 ` Re: Get A Tree from a table Joe Conway <mail@joeconway.com>
0 siblings, 2 replies; 3+ messages in thread
From: gzscott2001@yahoo.ca @ 2002-10-06 05:37 UTC (permalink / raw)
To: pgsql-sql
I like to do something like this:(build a tree from relationship)
I have a table like this
NODES__________________________________
ParentID Varchar(20)
ParentType varchar(20)
ChildID varchar(20)
ChildType varchar(20)
__________________________________
What in the table
'A1', 'A', 'B1', 'B'
'A2', 'A', 'B2', 'B'
'A1', 'A', 'B3', 'B'
'B1', 'B', 'C1', 'C'
'B1', 'B', 'C2', 'C'
'C1', 'C', 'D1', 'D'
'A1', 'A', 'B4', 'B'
'B1', 'B', 'C5', 'C'
----------------------------------
now I like to get all nodes with 'A1' as root
to get the result like this
'A1', 'A', 'B1', 'B'
'B1', 'B', 'C1', 'C'
'C1', 'C', 'D1', 'D'
'B1', 'B', 'C2', 'C'
'B1', 'B', 'C5', 'C'
'A1', 'A', 'B3', 'B'
'A1', 'A', 'B4', 'B'
or I just like to get another tree start with 'B1'
like this
'B1', 'B', 'C1', 'C'
'C1', 'C', 'D1', 'D'
'B1', 'B', 'C2', 'C'
'B1', 'B', 'C5', 'C'
How can i make it by sql , and sql functions
Thanks lot and regards.
Scott
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Get A Tree from a table
2002-10-06 05:37 Get A Tree from a table Scott Yaung <gzscott2001@yahoo.ca>
@ 2002-10-07 16:40 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2002-10-07 16:40 UTC (permalink / raw)
To: gzscott2001@yahoo.ca; pgsql-sql
Scott,
> NODES__________________________________
> ParentID Varchar(20)
> ParentType varchar(20)
> ChildID varchar(20)
> ChildType varchar(20)
This looks difficult... mainly becuase, in your shema, a "parent" node
can be its own child or grandchild, resulting in an endless loop.
If that was not your intention, you may wish to think of using a
different structure.
There are two main ways for you to approach this problem in 7.2.x:
1) Buy Joe Celko's "SQL for Smarties" and read up on pure-SQL tree
implementations, which he covers in far more depth than I want to here;
2) Explore the tree module in /contrib in your Postgresql source.
I understand from Joe Conway that in 7.3, you will have another choice,
as Postgres will offer support for Oracle's tree-like "IS CONNECTED BY"
expression.
Good luck!
-Josh Berkus
P.S. also, there's some articles on tree structures up at techdocs:
http://techdocs.postgresql.org/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Get A Tree from a table
2002-10-06 05:37 Get A Tree from a table Scott Yaung <gzscott2001@yahoo.ca>
@ 2002-10-07 16:54 ` Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 3+ messages in thread
From: Joe Conway @ 2002-10-07 16:54 UTC (permalink / raw)
To: Scott Yaung <gzscott2001@yahoo.ca>; +Cc: pgsql-sql
Scott Yaung wrote:
> I like to do something like this:(build a tree from relationship)
[snip]
> How can i make it by sql , and sql functions
> Thanks lot and regards.
There have been quite a few discussions of this topic in the past, so I would
suggest you search through the archives.
In 7.3 (currently in beta) you can use contrib/ltree or contrib/tablefunc.
Here's an example of using the connectby() function from contrib/tablefunc:
CREATE TABLE nodes(parentid varchar(20), parenttype varchar(20), childid
varchar(20), childtype varchar(20));
INSERT INTO nodes values('A1', 'A', 'B1', 'B');
INSERT INTO nodes values('A2', 'A', 'B2', 'B');
INSERT INTO nodes values('A1', 'A', 'B3', 'B');
INSERT INTO nodes values('B1', 'B', 'C1', 'C');
INSERT INTO nodes values('B1', 'B', 'C2', 'C');
INSERT INTO nodes values('C1', 'C', 'D1', 'D');
INSERT INTO nodes values('A1', 'A', 'B4', 'B');
INSERT INTO nodes values('B1', 'B', 'C5', 'C');
test=# SELECT * FROM connectby('nodes','childid','parentid','A1',0,'~') AS
t(childid varchar, parentid varchar, level int, branch text);
childid | parentid | level | branch
---------+----------+-------+-------------
A1 | | 0 | A1
B1 | A1 | 1 | A1~B1
C1 | B1 | 2 | A1~B1~C1
D1 | C1 | 3 | A1~B1~C1~D1
C2 | B1 | 2 | A1~B1~C2
C5 | B1 | 2 | A1~B1~C5
B3 | A1 | 1 | A1~B3
B4 | A1 | 1 | A1~B4
(8 rows)
test=# SELECT * FROM connectby('nodes','childid','parentid','B1',0,'~') AS
t(childid varchar, parentid varchar, level int, branch text);
childid | parentid | level | branch
---------+----------+-------+----------
B1 | | 0 | B1
C1 | B1 | 1 | B1~C1
D1 | C1 | 2 | B1~C1~D1
C2 | B1 | 1 | B1~C2
C5 | B1 | 1 | B1~C5
(5 rows)
HTH,
Joe
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-10-07 16:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-06 05:37 Get A Tree from a table Scott Yaung <gzscott2001@yahoo.ca>
2002-10-07 16:40 ` Josh Berkus <josh@agliodbs.com>
2002-10-07 16:54 ` Joe Conway <mail@joeconway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox