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

zqhxuyuan

项目中用户组,设备组采用树. 表结构是最简单的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*/DECLAREret integer;BEGINRETURN NEXT $1;IF $2 = 0 THENFOR ret IN SELECT groupid FROM som_user_group_info WHERE parentid = $1 LOOPFOR ret IN select * from getchild_set(ret,0) LOOPRETURN NEXT ret;END LOOP;END LOOP;ELSEFOR ret IN SELECT id FROM som_device_group_info WHERE parentid = $1 LOOPFOR ret IN select * from getchild_set(ret,1) LOOPRETURN 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*/declareret character varying := '';tmp integer;beginIF $2 = 0 THENfor tmp in select groupid from som_user_group_info where parentid = $1 loopret = ret || ',' || getchild_str(tmp,0);end loop;ELSEfor tmp in select id from som_device_group_info where parentid = $1 loopret = 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 ASDECLAREitemid ALIAS FOR $1;itemrecord record;BEGINSELECT 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  THENfor itemrecord in SELECT s.* FROM public.menus s  where s.parent_id=itemrecord.id LOOPfor itemrecord in select * from public.query_child_menus (itemrecord.id) LOOPRETURN 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$DECLAREret record;BEGINselect * 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 THENFOR ret INselect * from som_user_group_info where parentid = $1 LOOPFOR ret INselect * from getchild_table(ret.groupid)LOOPRETURN 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$DECLAREret record;BEGINselect groupid,parentid,groupname into ret from som_user_group_info where groupid = $1;RETURN NEXT ret;FOR ret INSELECT groupid,parentid,groupname FROM som_user_group_info WHERE parentid = $1 LOOPFOR ret INselect * from getchild_record(ret.groupid) as foo(groupid integer,parentid integer,groupname varchar)LOOPRETURN 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$DECLAREret integer;BEGINRETURN NEXT $1;FOR ret INSELECT groupid FROM som_user_group_info WHERE parentid = $1 LOOPFOR ret INselect * from getchild_set(ret) LOOPRETURN NEXT ret;END LOOP;END LOOP;END;$BODY$LANGUAGE 'plpgsql'--*****递归树设计*****--CREATE OR REPLACE FUNCTION getchild_set(integer) RETURNS SETOF integer AS$BODY$DECLAREret integer;--ret1 integer;BEGINRETURN 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) LOOPRETURN NEXT ret1;END LOOP;--步骤3: ret1可以统一用ret变量FOR ret IN select * from getchild_set(ret) LOOPRETURN NEXT ret;END LOOP;END LOOP;END;$BODY$LANGUAGE 'plpgsql'--返回字符串形式--步骤1:CREATE OR REPLACE FUNCTION recursive_child(integer) RETURNS character varying AS$BODY$declareret character varying := '';tmp integer;beginfor 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,4CREATE OR REPLACE FUNCTION getchild_str(integer)  RETURNS character varying AS$BODY$declarebeginreturn $1 || recursive_child($1);end;$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100;  --将上面2个函数合为一个,并加入类型CREATE OR REPLACE FUNCTION getchild_str(integer) RETURNS character varying AS$BODY$declareret character varying := '';tmp integer;beginfor tmp in select groupid from som_user_group_info where parentid = $1 loopret = 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