[PGS9.2] Impementation CTE or BOM

/************************************************************************************************
-- Title : [PGS9.2] Impementation CTE or BOM
-- Reference : dbrang.tistory.com
-- Key word : postgresql cte bom recursive
************************************************************************************************/

-- initialization

DROP TABLE empTbl;

-- createing AND inserting data

CREATE TABLE empTbl 

( emp CHAR(3)

, manager CHAR(3)

, department CHAR(3)

);

INSERT INTO empTbl VALUES('나사장',NULL,NULL);

INSERT INTO empTbl VALUES('김재무','나사장','재무부');

INSERT INTO empTbl VALUES('김부장','김재무','재무부');

INSERT INTO empTbl VALUES('이부장','김재무','재무부');

INSERT INTO empTbl VALUES('우대리','이부장','재무부');

INSERT INTO empTbl VALUES('지사원','이부장','재무부');

INSERT INTO empTbl VALUES('이영업','나사장','영업부');

INSERT INTO empTbl VALUES('한과장','이영업','영업부');

INSERT INTO empTbl VALUES('최정보','나사장','정보부');

INSERT INTO empTbl VALUES('윤차장','최정보','정보부');

INSERT INTO empTbl VALUES('이주임','윤차장','정보부');

-- confirming data

SELECT * FROM empTbl;

-- implementation of BOM LEVEL 

WITH RECURSIVE empCTE(empName, mgrName, dept, level)

AS

( -- 앵커 멤버(Anchor Member)

    SELECT emp, manager, department , 0 

    FROM empTbl

    WHERE manager IS NULL -- 상관이 없는 사람이 바로 사장

    UNION ALL

  

    -- 재귀 멤버(Recursive Member)

    SELECT AA.emp, AA.manager, AA.department, BB.level+1

    FROM empTbl AS AA 

    INNER JOIN empCTE AS BB

    ON AA.manager = BB.empName

)

SELECT * 

FROM empCTE 

ORDER BY dept, level;

-- implementation of BOM Level AND Indentation.1

WITH RECURSIVE empCTE(empName, mgrName, dept, level)

AS

(

     SELECT emp, manager, department , 0 

     FROM empTbl

     WHERE manager IS NULL -- 사장

     UNION ALL

     SELECT AA.emp, AA.manager, AA.department, BB.level+1

     FROM empTbl AS AA INNER JOIN empCTE AS BB

     ON AA.manager = BB.empName

)

SELECT /*replicate(' ┗', level)*/'L' || empName AS "직원이름", dept AS "직원부서"

FROM empCTE  

ORDER BY dept, level;

-- implementation of BOM Level AND Indentation.2

WITH RECURSIVE empCTE(empName, mgrName, dept, level)

AS

(

     SELECT emp, manager, department , 0 

     FROM empTbl

     WHERE manager IS NULL -- 사장

     UNION ALL

     SELECT AA.emp, AA.manager, AA.department, BB.level+1

     FROM empTbl AS AA INNER JOIN empCTE AS BB

     ON AA.manager = BB.empName

     WHERE level < 2

)

SELECT /*replicate(' ┗', level)*/'L' || empName AS "직원이름", dept "직원부서"

FROM empCTE  

ORDER BY dept, level;

/* WHEN implement making a identation, 

   don't know how TO replicate char like SQL Server replicate function*/