Re: stumped on a with recursive example

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'?

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.

>
>
> 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.
>
>
Thank you for the reply, that looks like it will help.