Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 8202E632934 for ; Fri, 10 Jul 2009 08:23:37 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 20536-05 for ; Fri, 10 Jul 2009 08:23:19 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from j83.attiksystem.ch (j83.attiksystem.ch [83.222.130.83]) by mail.postgresql.org (Postfix) with ESMTP id 74D80631EAC for ; Fri, 10 Jul 2009 08:23:26 -0300 (ADT) Received: from cust.static.213-200-238-69.cybernet.ch ([213.200.238.69] helo=email.attiksystem.ch) by j83.attiksystem.ch with esmtp (Exim 4.63) (envelope-from ) id 1MPECI-00086h-CS for pgsql-sql@postgresql.org; Fri, 10 Jul 2009 13:23:22 +0200 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Subject: Re: WITH RECURSION output ordering with trees X-MimeOLE: Produced By Microsoft Exchange V6.0.6619.12 Date: Fri, 10 Jul 2009 13:09:13 +0200 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] WITH RECURSION output ordering with trees Thread-index: AcoBQENg5GS0MQjhTMOooFxAnyDGqwADz38g References: From: "Philippe Lang" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200907/56 X-Sequence-Number: 32797 pgsql-sql-owner@postgresql.org wrote: > Hi, >=20 > I'm playing with the new "WITH RECURSIVE" feature of 8.4. I'm trying > to figure out how to use it with trees.=20 >=20 > Here is the test code I use: >=20 > --------------------------------------------------------- > --DROP TABLE recursion; >=20 > CREATE TABLE recursion > ( > id serial, > lookup varchar(16), > parent_id integer, > primary key(id), > foreign key(parent_id) references recursion(id) ); >=20 > INSERT INTO recursion VALUES(1, 'a1', NULL); > INSERT INTO recursion VALUES(2, 'b11', 1); > INSERT INTO recursion VALUES(645, 'c111', 2); > INSERT INTO recursion VALUES(823, 'c112', 2); > INSERT INTO recursion VALUES(243, 'c113', 2); > INSERT INTO recursion VALUES(6, 'b12', 1); > INSERT INTO recursion VALUES(845, 'c121', 6); > INSERT INTO recursion VALUES(583, 'c122', 6); > INSERT INTO recursion VALUES(9, 'b13', 1); > INSERT INTO recursion VALUES(10, 'c131', 9); >=20 > WITH RECURSIVE parse_tree (depth, id, lookup, parent_id) AS ( > SELECT > 0, > parent.id, > parent.lookup, > parent.parent_id > FROM recursion AS parent > WHERE parent_id IS NULL >=20 > UNION ALL >=20 > SELECT > parent.depth + 1, > child.id, > child.lookup, > child.parent_id > FROM parse_tree parent, recursion AS child > WHERE child.parent_id =3D parent.id > ) >=20 > SELECT * FROM parse_tree; > --------------------------------------------------------- >=20 > Here is the result: >=20 > depth | id | lookup | parent_id > -------+-----+--------+----------- > 0 | 1 | a1 | > 1 | 2 | b11 | 1 > 1 | 6 | b12 | 1 > 1 | 9 | b13 | 1 > 2 | 645 | c111 | 2 > 2 | 823 | c112 | 2 > 2 | 243 | c113 | 2 > 2 | 845 | c121 | 6 > 2 | 583 | c122 | 6 > 2 | 10 | c131 | 9 >=20 > I'd like to perform a real recursion, and show the tree structure in > a more appopriate way, like this:=20 >=20 > depth | id | lookup | parent_id > -------+-----+--------+----------- > 0 | 1 | a1 | > 1 | 2 | b11 | 1 > 2 | 645 | c111 | 2 > 2 | 823 | c112 | 2 > 2 | 243 | c113 | 2 > 1 | 6 | b12 | 1 > 2 | 845 | c121 | 6 > 2 | 583 | c122 | 6 > 1 | 9 | b13 | 1 > 2 | 10 | c131 | 9 >=20 > Any idea how to do that? (without trying to sort on the lookup > column, whose values can be random outside this test)=20 Hi again, I reply to my own post: I found a way to parse the tree with the help of the tablefunc contrib package: --------------------------------------------------------- SELECT t.depth, t.id, r.lookup, t.parent_id FROM connectby('recursion', 'id', 'parent_id', 'lookup', '1', 0) AS t(id integer, parent_id integer, depth integer, o integer) INNER JOIN recursion AS r ON t.id =3D r.id --------------------------------------------------------- depth | id | lookup | parent_id -------+-----+--------+----------- 0 | 1 | a1 | 1 | 2 | b11 | 1 2 | 645 | c111 | 2 2 | 823 | c112 | 2 2 | 243 | c113 | 2 1 | 6 | b12 | 1 2 | 845 | c121 | 6 2 | 583 | c122 | 6 1 | 9 | b13 | 1 2 | 10 | c131 | 9 I guess this is hard to achieve with a "WITH RECURSIVE" call. So my question is now: is the inclusion of "START WITH... CONNECT BY" planned for Postgresql? I read a patch had been developed for Postgresql 8.3: http://www.postgresql-support.de/blog/blog_hans.html Best regards, Philippe Lang