From: Philippe Lang <philippe.lang@attiksystem.ch>
To: Harald Fuchs <hari.fuchs@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: WITH RECURSION output ordering with trees
Date: Fri, 10 Jul 2009 15:05:49 +0200
Message-ID: <E6A0649F1FBFA3408A37F505400E7AC21F8564@email.attiksystem.ch> (raw)
References: <E6A0649F1FBFA3408A37F505400E7AC215CE67@email.attiksystem.ch>
<h378b6$drn$1@ger.gmane.org>
<E6A0649F1FBFA3408A37F505400E7AC215CE69@email.attiksystem.ch>
<puprc8wwcz.fsf@srv.protecting.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: philippe.lang@attiksystem.ch, hari.fuchs@gmail.com
Subject: Re: WITH RECURSION output ordering with trees
In-Reply-To: <E6A0649F1FBFA3408A37F505400E7AC21F8564@email.attiksystem.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox