Re: 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.