Re: WITH RECURSIVE question

great,

many thanks for the excellent blog entry.

Marc Mamin

> -----Original Message-----
> From: depesz(at)depesz(dot)com [mailto:depesz(at)depesz(dot)com]
> Sent: Freitag, 13. Juli 2012 12:52
> To: Marc Mamin
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 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/