Re: WITH RECURSIVE clause -- all full and partial paths - Mailing list pgsql-general

In article <aaf543e90906120856r5219cf9cv7f13ba0d37494378@mail.gmail.com>,
aryoo <howaryoo@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)