WITH RECURSIVE

• WITH의 수행 결과를 다시 자신 또는 다음 WITH 내에서 참조할 수 있다.
• WITH RECURSIVE {CTE 이름} [ ( {컬럼 목록 } ) ] 형태에서 지정한 CTE 이름, 컬럼 이름으로 다음 재귀 수행 시 참조할 수 있다.

형식

WITH RECURSIVE CTE [ ( {컬럼 목록} ) ] AS (

    SELECT {컬럼 목록}   -- 앵커 멤버(재귀 시작 집합 정의)
      FROM {테이블}
     WHERE {재귀 시작 조건}

    UNION ALL

    SELECT {컬럼 목록}   -- 재귀 멤버
      FROM CTE
     WHERE {재귀 종료 조건}
)
SELECT *
  FROM CTE;

예제 : 1 ~ 10 누적

WITH RECURSIVE CTE AS
(
    SELECT 1 AS N

    UNION ALL

    SELECT N + 1 AS N
      FROM CTE
     WHERE N < 10
)
SELECT * FROM CTE;

WITH RECURSIVE CTE(N, M) AS (

    SELECT 1
          ,2

    UNION ALL

    SELECT N + 1
          ,M + 2
      FROM CTE

     WHERE N < 10
)
SELECT *
  FROM CTE;

예제 : 트리 구조 출력

[1] 테이블, 데이터 정의

DROP TABLE IF EXISTS SAMPLE_TREE;
CREATE TABLE SAMPLE_TREE (
    ID        INT PRIMARY KEY
   ,PARENT_ID INT NULL
   ,LV        INT NOT NULL

   ,FOREIGN KEY (PARENT_ID) REFERENCES SAMPLE_TREE(ID)
);

INSERT INTO SAMPLE_TREE (VALUES
    (1  , NULL, 1)
   ,(2  , NULL, 1)

   ,(11 , 1   , 2)
   ,(12 , 1   , 2)

   ,(21 , 2   , 2)
   ,(22 , 2   , 2)
   ,(23 , 2   , 2)

   ,(111, 11  , 3)
   ,(112, 11  , 3)
   ,(113, 11  , 3)
   ,(114, 11  , 3)
);

[2] 조회 쿼리 1 : PID, ID 출력

WITH RECURSIVE CTE(ID, PID) AS (

    SELECT ID, PARENT_ID AS PID
      FROM SAMPLE_TREE
     WHERE ID = '1' -- 트리 루트 조건 설정

    UNION ALL

    SELECT S.ID, S.PARENT_ID AS PID
      FROM SAMPLE_TREE S, CTE
     WHERE S.PARENT_ID = CTE.ID
)
SELECT PID, ID
  FROM CTE

[3] 조회 쿼리 3 : PPID, PID, ID, 계층 구조 출력

WITH RECURSIVE CTE(ID, PID, PPID, HIERARCHY, LV) AS (

    SELECT ID        AS ID
          ,PARENT_ID AS PID
          ,NULL::INT AS PPID
          ,ID::TEXT  AS HIERARCHY  -- 계층 관계를 누적하여 표시
          ,LV        AS LV
      FROM SAMPLE_TREE
     WHERE ID = '1' -- 트리 루트 조건 설정

    UNION ALL -- 불필요하게 중복 제거 기능 사용하지 않도록 UNION 대신 UNION ALL 사용

    SELECT S.ID        AS ID
          ,S.PARENT_ID AS PID
          ,CTE.PID     AS PPID
          ,CTE.HIERARCHY || ' > ' || S.ID::TEXT AS HIERARCHY -- 누적
          ,S.LV        AS LV
      FROM SAMPLE_TREE S, CTE
     WHERE S.PARENT_ID = CTE.ID
)
SELECT LV, PPID, PID, ID, HIERARCHY
  FROM CTE