PostgreSQL RECURSIVE 사용 (재귀 쿼리)

메뉴 트리 등을 생성할 때 유용

WITH RECURSIVE tree_menu(menu_id, menu_nm, level, menu_path, cycle) AS
(
    SELECT menu_id
         , menu_nm
         , 0
         , array[menu_id::TEXT]
         , false
      FROM menu
     WHERE parent_menu_id IS NULL
     UNION ALL
    SELECT A.menu_id
         , A.menu_nm
         , level + 1
         , menu_path || A.menu_id::TEXT
         , A.menu_id = any(menu_path)
      FROM menu A, tree_menu B
     WHERE A.parent_menu_id = B.menu_id
       AND NOT cycle
)
SELECT menu_id
     , menu_nm
     , level
     , ARRAY_TO_STRING(menu_path, '$')
  FROM tree_menu