SHARE
TWEET

Untitled

a guest Jun 19th, 2017 51 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE department (
  2.     id INTEGER PRIMARY KEY,  -- department ID
  3.     parent_department INTEGER REFERENCES department, -- upper department ID
  4.     name TEXT -- department name
  5. );
  6.  
  7. INSERT INTO department (id, parent_department, "name")
  8. VALUES
  9.      (0, NULL, 'ROOT'),
  10.      (1, 0, 'A'),
  11.      (2, 1, 'B'),
  12.      (3, 2, 'C'),
  13.      (4, 2, 'D'),
  14.      (5, 0, 'E'),
  15.      (6, 4, 'F'),
  16.      (7, 5, 'G');
  17.  
  18.  
  19. WITH RECURSIVE subdepartment AS
  20. (
  21.     -- non-recursive term
  22.     SELECT DISTINCT
  23.              d.parent_department      AS parent
  24.            , d.id                     AS child
  25.            , d.name                   AS name
  26.            , ''                       AS chain
  27.     FROM   department d
  28.  
  29.     UNION ALL
  30.  
  31.     -- recursive term
  32.     SELECT   d.parent_department      AS parent
  33.            , d.id                     AS child
  34.            , d.name                   AS name
  35.            , --regexp_replace(
  36.              --   (chain ||
  37.              --      (case
  38.              --          when d.name is null
  39.              --          then '.'
  40.              --          else '.'||d.name
  41.              --       end)
  42.              --   )
  43.              --   , E'^\\.'
  44.              --   , ''
  45.              --)                        AS chain
  46.              CASE
  47.                 WHEN chain IS NULL OR chain = ''
  48.                 THEN sd.name
  49.                 ELSE sd.name || '.' || chain
  50.              END                        AS chain
  51.  
  52.     FROM   department                 AS d
  53.     JOIN   subdepartment              AS sd
  54.       --ON   (d.parent_department = sd.child)
  55.       ON   (d.id = sd.parent)
  56. )
  57. SELECT   ROW_NUMBER() OVER (ORDER BY chain),parent,child,name,chain
  58. FROM     subdepartment
  59. --WHERE    chain != '' or parent is null
  60. ORDER BY chain,child ;
RAW Paste Data
Pastebin PRO Summer Special!
Get 40% OFF on Pastebin PRO accounts!
Top