Thread: stumped on a with recursive example

On 2011/12/02 17:12, Henry Drexler wrote:
> on http://wiki.postgresql.org/wiki/CTEReadme
>
> It shows this:
>
> INSERT INTO department (id, parent_department,"name")
> VALUES
>       (0, NULL,'ROOT'),
>       (1, 0,'A'),
>       (2, 1,'B'),
>       (3, 2,'C'),
>       (4, 2,'D'),
>       (5, 0,'E'),
>       (6, 4,'F'),
>       (7, 5,'G');
>
> -- department structure represented here is as follows:
> --
> -- ROOT-+->A-+->B-+->C
> --      |         |
> --      |         +->D-+->F
> --      +->E-+->G
>
>
> I have ran the recursive query and indeed it only is showing A,B,C,D,F and
> not E,G as shown in the graphic above.  So postgres is understanding the
> structure - however I am not.
>
> What I am not getting/seeing is how one is getting the F to come after the
> D and the G after the E.
>
>    I can see A and E both have a 0 so they branch off of the Null, but why
> is the 4,'F' being attached to the 2,'D' and the 5,'G' being attached to
> the 0,'E'?

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.