Skip site navigation (1) Skip section navigation (2)

Re: stumped on a with recursive example

From: Johan Nel <johan(dot)nel(at)xsinet(dot)co(dot)za>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: stumped on a with recursive example
Date: 2011-12-02 15:29:44
Message-ID: jbaqts$4ud$1@dont-email.me (view raw or flat)
Thread:
Lists: pgsql-novice
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.

In response to

Responses

pgsql-novice by date

Next:From: Henry DrexlerDate: 2011-12-02 16:28:07
Subject: Re: stumped on a with recursive example
Previous:From: Henry DrexlerDate: 2011-12-02 15:12:52
Subject: stumped on a with recursive example

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group