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
Date: Fri, 10 Jul 2009 13:50:22 +0200
Message-ID: <E6A0649F1FBFA3408A37F505400E7AC215CE69@email.attiksystem.ch>
Thread-Topic: [SQL] WITH RECURSION output ordering with trees
Thread-index: AcoBTvHOBtETEuGQSuezQPvgotCjVQAAvlyw
References: <E6A0649F1FBFA3408A37F505400E7AC215CE67@email.attiksystem.ch>
	<h378b6$drn$1@ger.gmane.org>
From: "Philippe Lang" <philippe.lang@attiksystem.ch>
To: "Thomas Kellerer" <spam_eater@gmx.net>,
	<pgsql-sql@postgresql.org>

pgsql-sql-owner@postgresql.org wrote:
> Philippe Lang, 10.07.2009 11:10:
>> 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
>> Here is the test code I use:
>>=20
>> I'd like to perform a real recursion, and show the tree structure in
>> a more appopriate way, like this:
>>=20
>> Any idea how to do that? (without trying to sort on the lookup
>> column, whose values can be random outside this test)
>=20
>=20
> The manual has a nice hint on this adding up IDs to "generate" a path
> like column that can be used for sorting.=20
>=20
> Try the following:
>=20
> WITH RECURSIVE parse_tree (depth, id, lookup, parent_id, sort_path)
>   AS ( SELECT 0,
>     parent.id,
>     cast(parent.lookup as text),
>     parent.parent_id,
>     array[0] as sort_path
>   FROM recursion_sample parent
>   WHERE parent_id IS NULL
>   UNION ALL
>   SELECT
>     parent.depth + 1,
>     child.id,
>     rpad(' ', depth * 2) || child.lookup,
>     child.parent_id,
>     parent.sort_path || child.id
>   FROM parse_tree parent JOIN recursion_sample child on
> child.parent_id =3D parent.id )
> select id, lookup
> from parse_tree
> order by sort_path
> ;
>=20
> This will output:
>=20
>  id  | lookup
> -----+--------
>    1 | a1
>    2 | b11
>  243 |   c113
>  645 |   c111
>  823 |   c112
>    6 | b12
>  583 |   c122
>  845 |   c121
>    9 | b13
>   10 |   c131
> (10 rows)

Hi Thomas,

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?

If yes, we would get instead:

 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

Best regards,

Philippe Lang