当在用oracle做递归时,往往会用到一个虚列level,表示当前的节点是第几层,现在PostgreSql的递归语法中(with recursive)中不包含层级列,当然你安装了PostgreSql的扩展也是可以的,就是安装cross这个脚本,具体在postgreSql安装目录项的share/extension下的tablefunc.sql,脚本。
由于在我实际的项目中不允许安装哪些脚本,因此只能先自定义一个函数,具体代码如下:
首先,创建自定义类型
CREATE TYPE public.type_custom_recursive AS ( keyid TEXT, branch TEXT, parent_keyid TEXT, level TEXT );
再创建函数:
CREATE OR REPLACE FUNCTION public.custom_recursive ( table_name varchar, keyid text, parent_keyid text, keyid_value text, level integer, split_value varchar ) RETURNS SETOF public.type_custom_recursive AS $body$ declare test_ type_custom_recursive; sql_text VARCHAR(1000); BEGIN -- i_id as keyid,id as branch,level as level if $5 = 0 THEN sql_text := 'select i_id::text as keyid,id::text as branch,i_parent_id::text as parent_keyid,level::text as level from ( with recursive temp as( select '||$2||' as i_id,'||$2||'::text as id,'||$3||' from '|| $1 ||' where '||$2||' = cast('||$4|| 'as int) union all select b.'||$2||',cast (a.id || '''||split_value||''' || b.'||$2||' as text) as id,b.'||$3||' from temp a,'||$1||' b where a.'||$2||' = b.'||$3||')select i_id,id,f_char_in_string(id,'''||$6||''')+1 as level,i_parent_id from temp ) t order by i_id'; else sql_text := 'select i_id::text as keyid,i_parent_id from temp ) t where level = '|| $5 ||' order by i_id'; end if; for test_ in execute sql_text loop return next test_; end loop; end; $body$ LANGUAGE 'plpgsql';
说明:
table_name varchar,--需要递归的表名 keyid text,--id parent_keyid text,--父id keyid_value text,--id的值 level integer,--层级 0的话显示当前id下的所有节点 split_value varchar -- 分隔符比如"->"
注意这里的id默认都是为整型的,如果id是varchar型那么需要稍作修改的。
下面做个小例:
select * fromcustom_recursive('organization','i_id','i_parent_id','1','->');
输出为
keyid,branch,parentkeyid,level
1 ,1,null,1
2,1->2,1,2
3,1->3,1,2 (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|