From: Philippe Lang <philippe.lang@attiksystem.ch>
To: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: WITH RECURSION output ordering with trees
Date: Fri, 10 Jul 2009 13:50:22 +0200
Message-ID: <E6A0649F1FBFA3408A37F505400E7AC215CE69@email.attiksystem.ch> (raw)
References: <E6A0649F1FBFA3408A37F505400E7AC215CE67@email.attiksystem.ch>
<h378b6$drn$1@ger.gmane.org>
pgsql-sql-owner@postgresql.org wrote:
> Philippe Lang, 10.07.2009 11:10:
>> Hi,
>>
>> I'm playing with the new "WITH RECURSIVE" feature of 8.4. I'm trying
>> to figure out how to use it with trees.
>>
>> Here is the test code I use:
>>
>> I'd like to perform a real recursion, and show the tree structure in
>> a more appopriate way, like this:
>>
>> Any idea how to do that? (without trying to sort on the lookup
>> column, whose values can be random outside this test)
>
>
> The manual has a nice hint on this adding up IDs to "generate" a path
> like column that can be used for sorting.
>
> Try the following:
>
> 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 = parent.id )
> select id, lookup
> from parse_tree
> order by sort_path
> ;
>
> This will output:
>
> 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
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, spam_eater@gmx.net
Subject: Re: WITH RECURSION output ordering with trees
In-Reply-To: <E6A0649F1FBFA3408A37F505400E7AC215CE69@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