> 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 Anagnostopoulos||Date: 2011-12-03 22:45:11|
|Subject: Linux server connection process consumes all memory|
|Previous:||From: Johan Nel||Date: 2011-12-02 15:29:44|
|Subject: Re: stumped on a with recursive example|