Recursive query in PostgreSQL | Code Factory

Reference Link : Link

Donate : Link

WITH RECURSIVE menu_tree AS (
SELECT mm.id,
mm.menu_name,
mm.menu_action,
mm.parent_menu_id,
mm.menu_order,
1 AS level,
ARRAY[mm.id] AS path_info
FROM menu_master mm
WHERE mm.parent_menu_id = 0
UNION ALL
SELECT c.id,
c.menu_name,
c.menu_action,
c.parent_menu_id,
c.menu_order,
p.level + 1,
(p.path_info || c.menu_order::bigint) || c.id
FROM menu_master c
JOIN menu_tree p ON c.parent_menu_id = p.id
)
SELECT mt.id,
mt.menu_name,
mt.menu_action,
mt.parent_menu_id,
mt.menu_order,
mt.level,
mt.path_info
FROM menu_tree mt
ORDER BY mt.path_info;