In article <aaf543e90906120856r5219cf9cv7f13ba0d37494378@xxxxxxxxxxxxxx>,
aryoo <howaryoo@xxxxxxxxx> writes:
> Dear list,
> In reference to the message below posted on the 'pgsql-hackers' list regarding
> 'iterative' queries,
> could anyone help me write the queries that return all full and all partial
> paths from the root?
Probably you want to use the following query:
WITH RECURSIVE subdepartment AS (
SELECT id, parent_department, name AS path
FROM department
WHERE name = 'A'
UNION ALL
SELECT d.id, d.parent_department, sd.path || '.' || d.name
FROM department d
JOIN subdepartment sd ON sd.id = d.parent_department
)
SELECT id, path
FROM subdepartment;
This returns:
id | path
----+---------
1 | A
2 | A.B
3 | A.B.C
4 | A.B.D
6 | A.B.C.F
(5 rows)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
