Re: WITH RECURSIVE question

On Fri, Jul 13, 2012 at 12:20:44PM +0200, Marc Mamin wrote:
> But How can I retrieve the complete structure in one query ?
> do I have to use a procedure for that ?
>
> Something like :
>
> WITH FOR_EACH (node) AS ( SELECT node from forest where parent IS NULL)
> SELECT * FROM (
> WITH RECURSIVE struc (pref, id, depth ) AS (
> SELECT '', node, 1 from forest where node= FOR_EACH.node
> UNION ALL
> SELECT (case when struc.pref= '' then '\' else struc.pref end )||
> '...' ,
> node,
> struc.depth +1
> FROM forest JOIN struc ON parent=struc.id
> )
> SELECT * FROM struc
> )one_tree
> ;

You can run the query you showed, with just slight modification:

WITH RECURSIVE struc (pref, id, depth ) AS (
SELECT '', node, 1 from forest where parent is null
UNION ALL
SELECT (case when struc.pref= '' then '\' else struc.pref end )||
'...' ,
node,
struc.depth +1
FROM forest JOIN struc ON parent=struc.id
)
SELECT * FROM struc;

But the result will most likely be *not* what you expected:

pref │ id │ depth
─────────┼────┼───────
│ 1 │ 1
│ 4 │ 1
\... │ 2 │ 2
\... │ 5 │ 2
\...... │ 3 │ 3
\...... │ 6 │ 3
(6 rows)

The problem is that you can't really order the rows in such a way that you wanted.

But check this:
http://www.depesz.com/2011/12/16/rtrees-recursive-trees-what-did-you-think-about/
Especially look for how "path" and "priority path" are constructed.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/