great, many thanks for the excellent blog entry. Marc Mamin > -----Original Message----- > From: depesz@xxxxxxxxxx [mailto:depesz@xxxxxxxxxx] > Sent: Freitag, 13. Juli 2012 12:52 > To: Marc Mamin > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
