PostgreSQL WITH RECURSIVE — 재귀 CTE로 트리 구조 탐색하기

-- 일반 CTE 예시
WITH active_users AS (
    SELECT * FROM users WHERE status = 'ACTIVE'
)
SELECT * FROM active_users WHERE created_at > '2026-01-01';

WITH RECURSIVE cte_name AS (
    -- ① Anchor: 시작 데이터 (반복문의 초기값)
    SELECT ...
 
    UNION  -- 또는 UNION ALL
 
    -- ② Recursive: 직전 단계의 새 행을 입력으로 다음 결과 생성
    SELECT ... FROM table JOIN cte_name ...
 
    -- ③ 종료: Recursive가 빈 결과를 반환하면 자동 종료
)
SELECT * FROM cte_name;

# 폴더 C(item_seq=4)에서 루트까지 역추적하는 예시
# 테이블 데이터: item_seq → {file_name, parent_no}
table = {
    1: {"file_name": "/", "parent_no": None},
    2: {"file_name": "A", "parent_no": 1},
    3: {"file_name": "B", "parent_no": 2},
    4: {"file_name": "C", "parent_no": 3},
}
 
working_table = [4]                          # Anchor: 시작 item_seq
result_table  = []
 
while working_table:                         # 빈 리스트가 되면 종료
    result_table.extend(working_table)       # UNION: 결과에 누적
    next_rows = []
    for seq in working_table:                # 직전 단계의 새 행만 처리
        parent_no = table[seq]["parent_no"]
        if parent_no is not None:
            next_rows.append(parent_no)
    working_table = next_rows                # 새로 찾은 행으로 교체
 
# result_table = [4, 3, 2, 1] → C, B, A, /

-- 예: parent_no = NULL인 루트에 도달하면
JOIN tree t ON p.item_seq = t.parent_no
-- t.parent_no = NULL → p.item_seq = NULL인 행 없음 → 빈 결과 → 종료

file_structure_t
┌──────────┬───────────┬───────────┐
│ item_seq │ file_name │ parent_no │
├──────────┼───────────┼───────────┤
│    1     │ /         │   NULL    │  ← 루트
│    2     │ A         │     1     │
│    3     │ B         │     2     │
│    4     │ C         │     3     │  ← 대상 파일이 여기 있음
└──────────┴───────────┴───────────┘
 
트리 구조:
/           (1)
└── A       (2)
    └── B   (3)
        └── C (4)  ← 대상 파일 존재 폴더

WITH RECURSIVE tree AS (
    -- Anchor: 대상 파일이 있는 폴더부터 시작
    SELECT item_seq, file_name, parent_no
    FROM file_structure_t
    WHERE item_seq = 4
 
    UNION
 
    -- Recursive: 부모 폴더를 찾아 올라감
    SELECT p.item_seq, p.file_name, p.parent_no
    FROM file_structure_t p
    JOIN tree t ON p.item_seq = t.parent_no
)
SELECT * FROM tree;

Working Table = [C]          Result Table = [C]

SELECT p.* FROM file_structure_t p
JOIN tree t ON p.item_seq = t.parent_no
--                    3    =    3  ✅ 매칭

Working Table = [B]          Result Table = [C, B]

Working Table = [A]          Result Table = [C, B, A]

Working Table = [/]          Result Table = [C, B, A, /]

SELECT * FROM tree:
┌──────────┬───────────┬───────────┐
│ item_seq │ file_name │ parent_no │
├──────────┼───────────┼───────────┤
│    4     │ C         │     3     │
│    3     │ B         │     2     │
│    2     │ A         │     1     │
│    1     │ /         │   NULL    │
└──────────┴───────────┴───────────┘
 
→ C에서 루트(/)까지 전체 경로가 한 번의 쿼리로 추출됨

대상 폴더: C (parent=B), D (parent=B)
→ B가 두 번 Anchor에 잡힘
→ B의 부모 A도 두 번 재귀됨
 
UNION:     A는 1번만 결과에 포함, 불필요한 재귀 반복 방지
UNION ALL: A가 2번 결과에 포함, 같은 경로를 중복 탐색

WITH
    cte_a AS (...),
    cte_b AS (... SELECT FROM cte_a ...),      -- ✅ cte_a 참조 가능
    cte_c AS (... SELECT FROM cte_a, cte_b ...) -- ✅ 둘 다 참조 가능
SELECT * FROM cte_c;

WITH
    cte_a AS (... SELECT FROM cte_b ...),  -- ❌ cte_b가 아직 정의 안 됨
    cte_b AS (...)

-- ✅ RECURSIVE 선언 → 자기 자신 참조 가능
WITH RECURSIVE tree AS (
    SELECT ... UNION SELECT ... FROM table JOIN tree ...
)
 
-- ❌ RECURSIVE 없음 → 자기 자신 참조 불가 (에러)
WITH tree AS (
    SELECT ... UNION SELECT ... FROM table JOIN tree ...
)

WITH RECURSIVE
    cte_a AS (... UNION SELECT ... FROM cte_a ...),  -- 재귀 ✅
    cte_b AS (... UNION SELECT ... FROM cte_b ...),  -- 재귀 ✅
    cte_c AS (SELECT 1 AS num)                       -- 일반 CTE ✅
SELECT ...

-- Anchor: file_full_path로 매칭 → 파일(COM_FLE) 레코드까지 포함
WHERE EXISTS (
    SELECT 1 FROM file_paths f
    WHERE f.file_path = s.file_full_path
)

-- 재귀 내부: 중복 허용
UNION ALL
 
-- 외부: 전체 결과에서 중복 제거
SELECT DISTINCT(a.*) FROM ( ... ) a
WHERE folder_type = 'COM_DIR'

SELECT DISTINCT(a.*) FROM (
    WITH RECURSIVE treeA AS ( ... )
    SELECT * FROM treeA
) a
WHERE folder_type = 'COM_DIR'

-- 일반 CTE: 대상 파일의 부모 디렉토리 ID만 추출
dir_with_files AS (
    SELECT DISTINCT s.parent_no AS item_seq
    FROM ... WHERE s.folder_type = 'COM_FLE'
)
 
-- Anchor: 디렉토리만 시작점으로 사용
WHERE d.item_seq IN (SELECT item_seq FROM dir_with_files)
  AND d.folder_type = 'COM_DIR'

UNION  -- UNION ALL 대신 UNION 사용

WITH RECURSIVE
    dir_with_files AS ( ... ),  -- 일반 CTE
    tree AS ( ... )             -- 재귀 CTE
SELECT * FROM tree ORDER BY file_full_path

WITH RECURSIVE tree AS (
    SELECT item_seq, parent_no FROM file_structure_t WHERE item_seq = 4
    UNION ALL
    SELECT p.item_seq, p.parent_no FROM file_structure_t p
    JOIN tree t ON p.item_seq = t.parent_no
)
CYCLE item_seq SET is_cycle USING path
SELECT * FROM tree WHERE NOT is_cycle;

WITH RECURSIVE tree AS (
    SELECT item_seq, parent_no, 1 AS depth
    FROM file_structure_t WHERE item_seq = 4
 
    UNION ALL
 
    SELECT p.item_seq, p.parent_no, t.depth + 1
    FROM file_structure_t p
    JOIN tree t ON p.item_seq = t.parent_no
    WHERE t.depth < 100  -- 최대 100단계
)
SELECT * FROM tree;