2011 项目笔记一. 数据库之向下递归取子组

项目中用户组,设备组采用树. 表结构是最简单的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