[PostgreSQL] WITH RECURSIVE 구문 - 재귀쿼리, 계층쿼리

PostgreSQL에서 WITH RECURSIVE 구문을 사용하여 반복문처럼 사용이 가능하다.

재귀적 쿼리인데 이는 보통 테이블 데이터가 계층형일때 많이 사용된다.

▷ 구문

WITH RECURSIVE recursive_name [(column1, ...)] AS (
-- initial query (처음 호출하는 쿼리)
-- non-recursive query
SELECT [(column1, ...)]
UNION [ALL]
-- recursive query (반복 쿼리)
SELECT [(column1, ...)] FROM recursive_name [WHERE]
)
-- parent query
SELECT * FROM recursive_name

WITH RECURSIVE 구문을 통해 쿼리가 반복되며,

반복된 결과를 parent query 영역에서 FROM 절로 가져와 사용하는 구조이다.

UNION 다음에 사용되는 recursive query 즉, 재귀쿼리문에서는 보통 where 조건을 통해 반복이 멈추도록 제한을 둔다.

구문에 대해 어느정도 알았다면 예제를 통해 접해보자.

▷ 예제1) WITH RECURSIVE 기본 사용법

WITH RECURSIVE mine_record AS (
   SELECT 1 AS num
   UNION ALL
   SELECT num + 1 FROM mine_record WHERE num < 10
)
SELECT * FROM mine_record

▷ 결과

▷ 예제2) 계층형 쿼리

- 계층형 쿼리를 진행하기에 앞서 이해를 돕기 위해 아래와 같은 데이터 구조를 가지고 있다고 가정하고 시작한다.

WITH RECURSIVE dept_record(dept_sn, parent_sn, dept_name, dept_ctn, level, path, cycle) 
AS(
   SELECT d.dept_sn, d.parent_sn, d.dept_name, d.dept_ctn, 0, ARRAY[d.dept_sn], false
   FROM dept d
   WHERE d.parent_sn IS NULL
   
   UNION ALL
   
   SELECT d.dept_sn, d.parent_sn, d.dept_name, d.dept_ctn, level + 1, path || d.dept_sn, d.dept_sn = ANY(path)
   FROM dept d, dept_record dr
   WHERE d.parent_sn = dr.dept_sn
   AND NOT CYCLE
)
SELECT dept_sn, parent_sn, dept_name, dept_ctn, level, path 
FROM dept_record
ORDER BY path

▷ 결과