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