From: | Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: WITH RECURSION output ordering with trees |
Date: | 2009-07-10 12:33:48 |
Message-ID: | puprc8wwcz.fsf@srv.protecting.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
In article <E6A0649F1FBFA3408A37F505400E7AC215CE69(at)email(dot)attiksystem(dot)ch>,
"Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Philippe Lang | 2009-07-10 13:05:49 | Re: WITH RECURSION output ordering with trees |
Previous Message | Philippe Lang | 2009-07-10 11:50:22 | Re: WITH RECURSION output ordering with trees |