WITH RECURSIVE question

["Marc Mamin" <M.Mamin@xxxxxxxxxxxx>]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general