项目中用户组,设备组采用树. 表结构是最简单的id+parentid来构造树.
功能点: 根据组id查询组下的所有子组.
此文首先给出最终版本,并分析如何实现.
--***** 数据库最终版本 *****--
CREATE OR REPLACE FUNCTION getchild_set(integer,integer) RETURNS SETOF integer AS
$BODY$
/*
根据组id查询所有子组.第二个参数为0:用户组; 为1:设备组
实现方式一: 返回integer集合类型(组id列表)
select getchild_set(1,0) as id
*/
DECLARE
ret integer;
BEGIN
RETURN NEXT $1;
IF $2 = 0 THEN
FOR ret IN SELECT groupid FROM som_user_group_info WHERE parentid = $1 LOOP
FOR ret IN select * from getchild_set(ret,0) LOOP
RETURN NEXT ret;
END LOOP;
END LOOP;
ELSE
FOR ret IN SELECT id FROM som_device_group_info WHERE parentid = $1 LOOP
FOR ret IN select * from getchild_set(ret,1) LOOP
RETURN NEXT ret;
END LOOP;
END LOOP;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql'
CREATE OR REPLACE FUNCTION getchild_str(integer,integer) RETURNS character varying AS
$BODY$
/*
根据组id查询所有子组.第二个参数为0:用户组; 为1:设备组
实现方式2: 返回逗号分隔的字符串
select getchild_str(1,0) as id
*/
declare
ret character varying := '';
tmp integer;
begin
IF $2 = 0 THEN
for tmp in select groupid from som_user_group_info where parentid = $1 loop
ret = ret || ',' || getchild_str(tmp,0);
end loop;
ELSE
for tmp in select id from som_device_group_info where parentid = $1 loop
ret = ret || ',' || getchild_str(tmp,1);
end loop;
END IF;
return $1 || ret;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;
------------------------------------------------------
postgretql 函数递归:
--①取得一棵树的所有孩子节点,包括自己.
WITH RECURSIVE r AS (
SELECT * FROM tree WHERE id = 1
union ALL
SELECT tree.* FROM tree, r WHERE tree.parent = r.id
)
SELECT * FROM r ORDER BY id;
--根据组id 向下递归查找所有子组(也包括自己)--针对用户组的SQL
WITH RECURSIVE r AS (
SELECT * FROM som_user_group_info WHERE groupid = 1
UNION ALL
SELECT t.* FROM som_user_group_info t, r
WHERE t.parentid = r.groupid
)
SELECT * FROM r ORDER BY groupid
但是这样只能写在sqlmap里或者直接在postgretql的控制台执行.不能写成类似函数的形式来复用.
不过如果要复用的话,写在一个统一的sqlmap里不是也可以,然后在需要的地方引用这个SQL片段.
问题又来了,因为后端也要用到这些东西,最好能在数据库层进行存储,而不是写在前端.
采用函数有2种形式:一是返回集合类型;二是返回字符串类型.
--③
CREATE OR REPLACE FUNCTION public.query_child_menus (integer) RETURNS SETOF public.menus AS
DECLARE
itemid ALIAS FOR $1;
itemrecord record;
BEGIN
SELECT s.* INTO itemrecord FROM som_user_group_info s where id=itemid;
RETURN NEXT itemrecord;
IF (select count(1) from public.menus s where s.parent_id=itemrecord.id) >0 THEN
for itemrecord in SELECT s.* FROM public.menus s where s.parent_id=itemrecord.id LOOP
for itemrecord in select * from public.query_child_menus (itemrecord.id) LOOP
RETURN NEXT itemrecord;
end LOOP;
end LOOP;
END IF;
RETURN;
END;
LANGUAGE 'plpgsql'
--返回Table集合类型:(som_user_group_info)
--select * from getchild_table(1)
CREATE OR REPLACE FUNCTION getchild_table(integer)
RETURNS SETOF som_user_group_info AS
$BODY$
DECLARE
ret record;
BEGIN
select * into ret from som_user_group_info where groupid = $1;
RETURN NEXT ret;
IF (select count(1) from som_user_group_info where parentid = $1) > 0 THEN
FOR ret IN
select * from som_user_group_info where parentid = $1
LOOP
FOR ret IN
select * from getchild_table(ret.groupid)
LOOP
RETURN NEXT ret;
END LOOP;
END LOOP;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql'
--返回RECORD集合类型:(groupid,parentid,groupname)
--select * from getchild_record(1) as foo(groupid integer,parentid integer,groupname varchar)
CREATE OR REPLACE FUNCTION getchild_record(integer)
RETURNS SETOF record AS
$BODY$
DECLARE
ret record;
BEGIN
select groupid,parentid,groupname into ret from som_user_group_info where groupid = $1;
RETURN NEXT ret;
FOR ret IN
SELECT groupid,parentid,groupname FROM som_user_group_info WHERE parentid = $1
LOOP
FOR ret IN
select * from getchild_record(ret.groupid) as foo(groupid integer,parentid integer,groupname varchar)
LOOP
RETURN NEXT ret;
END LOOP;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql'
--返回integer集合类型(groupid) usergroup版本
--select getchild_set(1)
CREATE OR REPLACE FUNCTION getchild_set(integer)
RETURNS SETOF integer AS
$BODY$
DECLARE
ret integer;
BEGIN
RETURN NEXT $1;
FOR ret IN
SELECT groupid FROM som_user_group_info WHERE parentid = $1
LOOP
FOR ret IN
select * from getchild_set(ret)
LOOP
RETURN NEXT ret;
END LOOP;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql'
--*****递归树设计*****--
CREATE OR REPLACE FUNCTION getchild_set(integer)
RETURNS SETOF integer AS
$BODY$
DECLARE
ret integer;
--ret1 integer;
BEGIN
RETURN NEXT $1;
FOR ret IN SELECT groupid FROM som_user_group_info WHERE parentid = $1 LOOP
--步骤1: 仅查询节点的下一级节点:直接返回遍历到的节点的子节点
--RETURN NEXT ret;
--Error: query has no destination for retult data
--select * from getchild_set(ret)
--RETURN NEXT ret;
--步骤2: 对子节点递归调用自身
FOR ret1 IN select * from getchild_set(ret) LOOP
RETURN NEXT ret1;
END LOOP;
--步骤3: ret1可以统一用ret变量
FOR ret IN select * from getchild_set(ret) LOOP
RETURN NEXT ret;
END LOOP;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql'
--返回字符串形式
--步骤1:
CREATE OR REPLACE FUNCTION recursive_child(integer) RETURNS character varying AS
$BODY$
declare
ret character varying := '';
tmp integer;
begin
for tmp in select groupid from som_user_group_info where parentid = $1 loop
--ret = ret || ',' || tmp;
--ret = ret || recursive_child(tmp);
ret = ret || ',' || tmp || recursive_child(tmp);
end loop;
return ret;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;
--步骤2: 上面的函数返回的字符串形式如:,2,3,4 再加上自身id在为:1,2,3,4
CREATE OR REPLACE FUNCTION getchild_str(integer)
RETURNS character varying AS
$BODY$
declare
begin
return $1 || recursive_child($1);
end;
$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100;
--将上面2个函数合为一个,并加入类型
CREATE OR REPLACE FUNCTION getchild_str(integer) RETURNS character varying AS
$BODY$
declare
ret character varying := '';
tmp integer;
begin
for tmp in select groupid from som_user_group_info where parentid = $1 loop
ret = ret || ',' || getchild_str(tmp);
end loop;
return $1 || ret;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;
------------------------------------------------------EOF
参考资料:
① http://blog.csdn.net/wh62592855/article/details/6443909
② http://www.postgretql.org/docs/8.4/static/queries-with.html
③ http://it.chinawin.net/database/article-facc.html