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

Re: stumped on a with recursive example

From: Henry Drexler <alonup8tb(at)gmail(dot)com>
To: Johan Nel <johan(dot)nel(at)xsinet(dot)co(dot)za>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: stumped on a with recursive example
Date: 2011-12-02 16:28:07
Message-ID: CAAtgU9R9BBjeELLH+K0gYSb6ySd+0cvphqonrTsJE4+Qm9Zwpw@mail.gmail.com (view raw or flat)
Thread:
Lists: 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.
>
>
Thank you for the reply, that looks like it will help.

In response to

pgsql-novice by date

Next:From: Ioannis AnagnostopoulosDate: 2011-12-03 22:45:11
Subject: Linux server connection process consumes all memory
Previous:From: Johan NelDate: 2011-12-02 15:29:44
Subject: Re: stumped on a with recursive example

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