In article <aaf543e90906120856r5219cf9cv7f13ba0d37494...@mail.gmail.com>, aryoo <howar...@gmail.com> 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general