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
