PostgreSql递归包含层级level

概述当在用oracle做递归时，往往会用到一个虚列level，表示当前的节点是第几层，现在PostgreSql的递归语法中(with recursive)中不包含层级列，当然你安装了PostgreSql的扩展也是可以的，就是安装cross这个脚本，具体在postgreSql安装目录项的share/extension下的tablefunc.sql,脚本。 由于在我实际的项目中不允许安装哪些脚本，因此只能

当在用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

总结

以上是内存溢出为你收集整理的PostgreSql递归包含层级level全部内容，希望文章能够帮你解决PostgreSql递归包含层级level所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。