[postgresql] connect by 구현

출처 : m.blog.naver.com/wiseyoun07/221135850258

 

with recursive MENU_V (MENU_ID, MENU_NM, UPPER_MENU_ID, MENU_ODR, dep, pth, cyc)
as (
	SELECT M.MENU_ID, M.MENU_NM, M.UPPER_MENU_ID, M.MENU_ODR, 1, 
		array[m.menu_ID::text]  AS pth, false
	FROM t_cm_menu M
	WHERE m.use_yn = 'Y' 
	  and m.MENU_ID = 'TOP'
	union all 
	select M.MENU_ID, M.MENU_NM, M.UPPER_MENU_ID, M.MENU_ODR, v.dep + 1,
	   array_append( v.pth, m.menu_id::text) AS pth , m.menu_id = any(v.pth)
	from t_cm_menu m, menu_v v
	where m.upper_menu_id = v.MENU_ID
	  and not cyc
	 
)
SELECT * 
from menu_v
order by pth