Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 041B0476109 for ; Mon, 7 Oct 2002 12:55:44 -0400 (EDT) Received: from joeconway.com (unknown [63.210.180.150]) by postgresql.org (Postfix) with ESMTP id 4D1AC4760B7 for ; Mon, 7 Oct 2002 12:55:43 -0400 (EDT) Received: from [206.19.64.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 3.5.9) with ESMTP-TLS id 1349038; Mon, 07 Oct 2002 09:29:12 -0700 Message-ID: <3DA1BC3D.2090204@joeconway.com> Date: Mon, 07 Oct 2002 09:54:22 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.1) Gecko/20020826 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Scott Yaung Cc: pgsql-sql@postgresql.org Subject: Re: Get A Tree from a table References: <341834e1.0210052137.7cbd6342@posting.google.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/93 X-Sequence-Number: 9811 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