CREATE TABLE department (
    id INTEGER PRIMARY KEY,  -- department ID
    parent_department INTEGER REFERENCES department, -- upper department ID
    name TEXT -- department name
);

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


WITH RECURSIVE subdepartment AS
(
    -- non-recursive term
    SELECT DISTINCT
             d.parent_department      AS parent
           , d.id                     AS child
           , d.name                   AS name
           , ''                       AS chain
    FROM   department d

    UNION ALL

    -- recursive term
    SELECT   d.parent_department      AS parent
           , d.id                     AS child
           , d.name                   AS name
           , --regexp_replace(
             --   (chain ||
             --      (case
             --          when d.name is null
             --          then '.'
             --          else '.'||d.name
             --       end)
             --   )
             --   , E'^\\.'
             --   , ''
             --)                        AS chain
             CASE
                WHEN chain IS NULL OR chain = ''
                THEN sd.name
                ELSE sd.name || '.' || chain
             END                        AS chain

    FROM   department                 AS d
    JOIN   subdepartment              AS sd
      --ON   (d.parent_department = sd.child)
      ON   (d.id = sd.parent)
)
SELECT   ROW_NUMBER() OVER (ORDER BY chain),parent,child,name,chain
FROM     subdepartment
--WHERE    chain != '' or parent is null
ORDER BY chain,child ;