To: pgsql-sql@postgresql.org
From: Harald Fuchs <hari.fuchs@gmail.com>
Subject: Re: WITH RECURSION output ordering with trees
Date: Fri, 10 Jul 2009 14:33:48 +0200
Lines: 37
Message-ID: <puprc8wwcz.fsf@srv.protecting.net>
References: <E6A0649F1FBFA3408A37F505400E7AC215CE67@email.attiksystem.ch>
	<h378b6$drn$1@ger.gmane.org>
	<E6A0649F1FBFA3408A37F505400E7AC215CE69@email.attiksystem.ch>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux)
Cancel-Lock: sha1:zczoI8F0948gey7fRTxcJVa+YIg=

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