Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id AB49E634374 for ; Fri, 10 Jul 2009 10:20:13 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 63539-06 for ; Fri, 10 Jul 2009 10:20:02 -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 29E85633582 for ; Fri, 10 Jul 2009 10:20:02 -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 1MPG18-00087o-L5 for pgsql-sql@postgresql.org; Fri, 10 Jul 2009 15:19:58 +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 15:05:49 +0200 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] WITH RECURSION output ordering with trees Thread-index: AcoBWMo5HQV4JD+USmacvTI6ti6IWAAB+Pkg References: A From: "Philippe Lang" To: "Harald Fuchs" , 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/60 X-Sequence-Number: 32801 pgsql-sql-owner@postgresql.org wrote: > In article > , > "Philippe Lang" writes:=20 >=20 >> Thanks for your answer. Si there a built-in function that would allow >> generating the sort path based on the value of the lookup column, >> instead of the id, which has no meaning at all? >=20 >> If yes, we would get instead: >=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 > Try this: >=20 > WITH RECURSIVE parse_tree (depth, id, lookup, parent_id, path) AS ( > SELECT 0, parent.id, parent.lookup, parent.parent_id, > parent.lookup::text FROM recursion AS parent > WHERE parent_id IS NULL > UNION ALL > SELECT parent.depth + 1, child.id, child.lookup, child.parent_id, > parent.path || '.' || child.lookup > FROM parse_tree parent > JOIN recursion AS child ON child.parent_id =3D parent.id > ) > SELECT depth, id, lookup, parent_id > FROM parse_tree > ORDER BY path Works great, thanks! Of course, concatenating lookups... Best regards, Philippe