pgsql 递归查询

-- 向上递归
--WITH RECURSIVE r AS (
--    SELECT * FROM crm_sys_menu WHERE id = ${subId}
--    union   ALL
--    SELECT crm_sys_menu.* FROM crm_sys_menu, r WHERE crm_sys_menu.id = r.p_id
--)
--SELECT * FROM r ORDER BY id;

-- 设置schema
SET search_path TO ${schema};
-- 移除函数
drop function if exists initMenu(int);
-- 创建函数
CREATE function initMenu(v_pid int) returns real
AS
$BODY$
begin
-- 向下递归
WITH RECURSIVE r AS (
    SELECT * FROM public.crm_sys_menu WHERE id = v_pid
    union ALL
    SELECT public.crm_sys_menu.* FROM public.crm_sys_menu, r WHERE public.crm_sys_menu.p_id = r.id
)
-- 插入菜单
insert into crm_sys_menu (SELECT * FROM r);

return 1;
end;
$BODY$
language plpgsql;

-- 调用传pid
select initMenu(${v_pid});