-- 向上递归
--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});