Postgresql 에서 계층형 query 구현

1. 완성된 예제

sql 실행에 따른 최종 화면은 Graha XML 레퍼런스 에서 확인할 수 있다.

WITH RECURSIVE graha_element_list (
    graha_element_id, type, parent_id, title, LEVEL, PATH, CYCLE
) AS (
    SELECT 
        g.graha_element_id, 
        g.type, 
        g.parent_id, 
        g.title, 
        0 as level, 
        array[g.graha_element_id] as path, 
        false as cycle
    FROM graha.graha_element g
    WHERE g.parent_id is null 
        and g.type = 'element'
    UNION ALL
    SELECT 
        g.graha_element_id, 
        g.type, 
        g.parent_id, 
        g.title, 
        LEVEL + 1 as level,
        PATH || case when g.type = 'element' then 1 else 0 end || g.graha_element_id as path, 
        g.graha_element_id = ANY(PATH) as cycle
    FROM graha.graha_element g, 
        graha_element_list sb
    WHERE g.parent_id = sb.graha_element_id 
        and NOT CYCLE
)
SELECT 
    graha_element_id,
    type,
    parent_id,
    lpad('', LEVEL, CHR(9)) || title as title,
    LEVEL,
    PATH
FROM graha_element_list
order by path

2. 설명

• 물리적인 테이블은 graha_element 1개이다.
• graha_element_list 는 논리적으로 정의된 것이다.
• WITH RECURSIVE 절의 UNION ALL 앞 부분은 최상위 게시물을 가져온다(parent_id is null).
• WITH RECURSIVE 절의 UNION ALL 아래 부분은 최상위 게시물을 제외한 나머지 게시물을 가져온다.
• type 은 attribute 혹은 element 인데, attribute 인 것들이 먼저 출력된다(UNION ALL 아래 부분에서 path 를 조합하는 부분 참조).
• title 앞 부분에 LEVEL 만큼 tab(CHR(9)) 을 붙인다(화면에서는 white-space:pre-wrap; 으로 공백이 유지되도록 하였다).

3. 응용

3.1. 정렬순서 변경

정렬순서를 변경하려면, 다음과 같이 한다.

top_id 와 관련된 것만 변경되었다.

WITH RECURSIVE graha_element_list (
    top_id, graha_element_id, type, parent_id, title, LEVEL, PATH, CYCLE
) AS (
    SELECT 
        g.graha_element_id as top_id,
        g.graha_element_id, 
        g.type, 
        g.parent_id, 
        g.title, 
        0 as level, 
        array[g.graha_element_id] as path, 
        false as cycle
    FROM graha.graha_element g
    WHERE g.parent_id is null 
        and g.type = 'element'
    UNION ALL
    SELECT 
        sb.top_id,
        g.graha_element_id, 
        g.type, 
        g.parent_id, 
        g.title, 
        LEVEL + 1 as level,
        PATH || case when g.type = 'element' then 1 else 0 end || g.graha_element_id as path, 
        g.graha_element_id = ANY(PATH) as cycle
    FROM graha.graha_element g, 
        graha_element_list sb
    WHERE g.parent_id = sb.graha_element_id 
        and NOT CYCLE
)
SELECT 
    graha_element_id,
    type,
    parent_id,
    lpad('', LEVEL, CHR(9)) || title as title,
    LEVEL,
    PATH
FROM graha_element_list
order by top_id desc, path