Re: stumped on a with recursive example - Mailing list pgsql-novice

I would use the following query:
WITH RECURSIVE search_graph AS (
 SELECT id, parent_department, "name", "name"::text as path, 0 AS depth
   FROM department d
   WHERE d.parent_department IS NULL
 UNION ALL
 SELECT r.id, r.parent_department, r."name", sg.path||'/'||r.id as path,
        sg.depth + 1 AS depth
   FROM department r, search_graph sg
   WHERE r.parent_department = sg.id
)
SELECT * FROM search_graph ORDER BY path;

Hopefully that will give you a better understanding of the structure of the return.

Johan Nel
Pretoria, South Africa.