[GENERAL] WITH RECURSIVE clause -- all full and partial paths

[aryoo]

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?

Sincerely,
Aryé.


--http://archives.postgresql.org/pgsql-hackers/2008-02/msg00642.php

CREATE TABLE department (
  id INT PRIMARY KEY,
  parent_department INT REFERENCES department,
  name TEXT
);

INSERT INTO department VALUES (0, NULL, 'ROOT');
INSERT INTO department VALUES (1, 0, 'A');
INSERT INTO department VALUES (2, 1, 'B');
INSERT INTO department VALUES (3, 2, 'C');
INSERT INTO department VALUES (4, 2, 'D');
INSERT INTO department VALUES (5, 0, 'E');
INSERT INTO department VALUES (6, 3, 'F');
INSERT INTO department VALUES (7, 5, 'G');

--select * from department
--delete from department

This will represent a tree structure of an organization:

  ROOT ---> A ---> B ---> C ---> F
    |              |
    |              +----> D
    |
    +-----> E ---> G




--If you want to extract all departments "under" A, you could use a
recursive query:

WITH RECURSIVE subdepartment AS
(
  --
  SELECT * FROM department WHERE name = 'A'

  UNION ALL

  -- recursive term referring to "subdepartment"
  SELECT d.* FROM department AS d, subdepartment AS sd
    --WHERE d.id = sd.parent_department
    WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment;

This will return A, B, C, D and F.