From: Joe Conway <mail@joeconway.com>
To: Scott Yaung <gzscott2001@yahoo.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Get A Tree from a table
Date: Mon, 07 Oct 2002 09:54:22 -0700
Message-ID: <3DA1BC3D.2090204@joeconway.com> (raw)
References: <341834e1.0210052137.7cbd6342@posting.google.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, gzscott2001@yahoo.ca
Subject: Re: Get A Tree from a table
In-Reply-To: <3DA1BC3D.2090204@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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