Re: WITH RECURSION output ordering with trees

pgsql-sql-owner@postgresql.org wrote:
> In article
> <E6A0649F1FBFA3408A37F505400E7AC215CE69@email.attiksystem.ch>,
> "Philippe Lang" <philippe.lang@attiksystem.ch> writes:
>
>> 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
>
> Try this:
>
> 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 = parent.id
> )
> SELECT depth, id, lookup, parent_id
> FROM parse_tree
> ORDER BY path

Works great, thanks! Of course, concatenating lookups...

Best regards,

Philippe