Thread: WITH RECURSIVE question
Hello,
WITH RECURSIVE queries are quite new for me, so I'm not sure if
following is possible
CREATE TEMP TABLE forest (node int,parent int);
INSERT INTO forest VALUES
(1, null),
(2, 1),
(3, 2),
(4, null),
(5, 4),
(6,5);
WITH RECURSIVE struc (pref, id, depth ) AS (
SELECT '', node, 1 from forest where node= 4
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;
(path,node,depth)
4 1
\... 5 2
\...... 6 3
This is fine as long as I start with a given node (here node= 4).
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
;
1 1
\... 2 2
\...... 3 3
4 1
\... 5 2
\...... 6 3
best regards,
Marc Mamin
Marc Mamin <M.Mamin@intershop.de> wrote: > WITH RECURSIVE struc (pref, id, depth ) AS ( > SELECT '', node, 1 from forest where node= 4 > 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; > > (path,node,depth) > 4 1 > \... 5 2 > \...... 6 3 > > This is fine as long as I start with a given node (here node= 4). > > But How can I retrieve the complete structure in one query ? Start with parent is null in the first query (instead of 'where node=4'). *untested* Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
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/
great, many thanks for the excellent blog entry. Marc Mamin > -----Original Message----- > From: depesz@depesz.com [mailto:depesz@depesz.com] > Sent: Freitag, 13. Juli 2012 12:52 > To: Marc Mamin > Cc: pgsql-general@postgresql.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/
Also this may be a bit more than you asked for but I just put up a blog entry a couple days going through CTE's generally and how we use them in LedgerSMB. You might find it helpful.