参考
1.参考石恒的代码案例
预期效果
1.控制递归层数
2.可以 DEBUG 追溯原因
3.期望达到通用效果
SCHEMA
CREATE SCHEMA oushu_toolkit;
测试表
create table demo (
ID varchar(10),
DSC varchar(100),
PID varchar(10)
);
insert Into demo values ('00001', '中国', '-1');
insert Into demo values ('00011', '陕西', '00001');
insert Into demo values ('00012', '贵州', '00001');
insert Into demo values ('00013', '河南', '00001');
insert Into demo values ('00111', '西安', '00011');
insert Into demo values ('00112', '咸阳', '00011');
insert Into demo values ('00113', '延安', '00011');
drop table if exists tbl_while;
create table tbl_while(
account_id text,
accout_type int,
sec_account_id text,
sec_accout_type int
) ;
insert into tbl_while
values
('xx' , 218 ,'x0' ,218),
('x0' , 218 ,'x1' ,218),
('x1' , 218 ,'x2' ,218),
('x2' , 210 ,'x3' ,218),
('x3' , 218 ,'x4' ,218),
('x4' , 218 ,'x5' ,218);
接收数据
drop table if exists oushu_toolkit.recv_tab_data;
create table oushu_toolkit.recv_tab_data(
parent_id text,
child_id text,
relation text,
level int
) with (appendonly=true, orientation=orc, compresstype=lz4);
函数主体
CREATE OR REPLACE FUNCTION oushu_toolkit.with_recursive
(
child_id text[] ,
table_name varchar,
child_id_name varchar,
parent_id_name varchar,
counter text ,
f_level text ,
where_condition text ,
end_condition text ,
f_debug_level int ,
recv_tab text
)
RETURNS text[] AS $$
/*
----------------------------------------------------------------------------------------------------------------
-- 版本 : 1.0.0
-- 作者 : fei.ye
-- 时间 : 2021-04-15
-- 参数 :
child_id : 儿子节点的值
table_name : 表名
child_id_name : 子节点字段名称
parent_id_name : 父节点字段名称
counter : 指定遍历的层级次数;超出不执行
f_level : 记录遍历的层数; 默认初初始为零
where_condition : 进入递归的条件
end_condition : 终止递归的条件
f_debug_level : 0 正常模式 ; 1 DEBUG模式
recv_tab : 接收结果数据的表
-- 返回值 :
0: 正常
1: 不正常
-- 涉及表范围 : 当前分区表
-- 功能 :
1.递归功能: 子节点查找父节点
2.可以设定指定的层级
-- 函数依赖关系 : none
-- 原理 : none
*/
declare
v_str text;
v_ids text[];
v_nums int default 0;
v_level int default 0;
v_where_condition text;
v_cnt int;
begin
-- 层次计数
v_level = f_level::int + 1 ;
-- 将设定的计数器的结束值N传递给计数器变量v_nums
v_nums = counter::int ;
-- 计数器开始工作; 每次减一
v_nums = v_nums - 1;
-- 当计数器的结束值N为-1;表示计数器无限制
if counter = -1 then
v_nums = -1;
raise notice 'INFO: 计数器 = [ % ],遍历无限制',-1;
end if;
-- 计数器=0 结束函数
if v_nums = 0 then
raise notice 'INFO: 计数器 = [ % ],结束递归',0;
return child_id;
end if;
-- 无限遍历结束条件
if(child_id is null) then
return null;
end if;
raise notice 'INFO: 计数器 = [ % ]',v_nums;
-- 模块:是否指定条件结束
if trim(lower(end_condition)) <> '-1' then
-- 存在强制结束条件
v_where_condition = end_condition
|| ' and ' ||parent_id_name
|| '=any(array[ '''|| replace(array_to_string(child_id,','),',',''',''') || '''])';
v_str ='select array_agg('|| child_id_name ||') from '
|| table_name
||' where '
|| v_where_condition;
if f_debug_level <> 0 then
raise notice '.';
raise notice 'DEBUG INFO:';
raise notice 'v_str:%',v_str;
end if;
if(v_str is not null) then execute v_str into v_ids;end if;
raise notice 'v_ids:%',v_ids;
if(v_ids is not null) then
return coalesce(v_ids,child_id) ;
end if;
end if;
---- 没有指定结束条件处理
v_where_condition = where_condition
|| ' and ' ||parent_id_name
|| '=any(array[ '''|| replace(array_to_string(child_id,','),',',''',''') || '''])';
v_str ='select array_agg('|| child_id_name ||') from '
|| table_name
||' where '
|| v_where_condition;
if f_debug_level <> 0 then
raise notice '.';
raise notice 'DEBUG INFO:';
raise notice 'v_str:%',v_str;
end if;
if(v_str is not null) then execute v_str into v_ids;end if;
raise notice 'v_ids:%',v_ids;
if f_debug_level <> 0 then
raise notice '.';
raise notice 'DEBUG INFO:';
v_str='insert into '
||recv_tab
||' select '||parent_id_name||','
||child_id_name||','
||parent_id_name ||'||''->''||'||child_id_name||','
||v_level
||' from '
|| table_name
||' where '
|| v_where_condition;
if(v_str is not null) then
raise notice 'v_str:%',v_str;
execute v_str ;
GET DIAGNOSTICS v_cnt = ROW_COUNT;
raise notice ' count : [ % ]' ,v_cnt;
v_str = 'update '||recv_tab
||' set relation=f1.relation'||'||''->''||'||recv_tab||'.child_id '
||' from '||recv_tab||' f1'
||' where f1.level+1 = '||recv_tab||'.level'
||' and f1.child_id = '||recv_tab||'.parent_id';
raise notice 'v_str:%',v_str;
execute v_str ;
GET DIAGNOSTICS v_cnt = ROW_COUNT;
raise notice ' update count : [ % ]' ,v_cnt;
end if;
end if;
--raise notice 'OUTIF v_ids:%',v_ids;
raise notice 'INFO: 进入递归 level = [ % ]',v_level;
v_str ='select c_sub_v_ids from (select '||'oushu_toolkit.with_recursive(array['''
|| replace(array_to_string(v_ids,','),',',''',''')
||'''],'''
||table_name ||''','''
||child_id_name ||''','''
||parent_id_name||''','''
||v_nums||''','''
||v_level||''','''
||where_condition||''','''
||end_condition||''','''
||f_debug_level::text||''','''
||recv_tab
||''') c_sub_v_ids) t';
if f_debug_level <> 0 then
raise notice '.';
raise notice 'DEBUG INFO:';
raise notice 'v_str:%',v_str;
end if;
if(v_str is not null) then execute v_str into v_ids;end if;
raise notice '退出递归 xx %',v_level;
return coalesce(v_ids,child_id);
end $$
LANGUAGE 'plpgsql';
使用方法
-- 默认调用方法
select oushu_toolkit.with_recursive(
array['xx'], -- 儿子节点的值
'tbl_while', -- 表名
'sec_account_id', -- 子节点字段名称
'account_id', -- 父节点字段名称
'-1', -- 指定遍历的层级次数;超出不执行
'0', -- 记录遍历的层数; 默认初初始为零
'accout_type=218', -- 进入递归的条件
'accout_type=210', -- 终止递归的条件
0, -- 0 正常模式 ; 1 DEBUG模式
'oushu_toolkit.recv_tab_data' -- 接收结果数据的关系表
);
-- DEBUG调试模式
truncate table oushu_toolkit.recv_tab_data;
select oushu_toolkit.with_recursive(
array['xx'], -- 儿子节点的值
'tbl_while', -- 表名
'sec_account_id', -- 子节点字段名称
'account_id', -- 父节点字段名称
'-1', -- -1 表示遍历无限制; 指定遍历的层级次数,超出不执行
'0', -- 记录遍历的层数; 默认初初始为零
'accout_type=218', -- 进入递归的条件
'accout_type=210', -- 终止递归的条件
1, -- 0 正常模式 ; 1 DEBUG模式
'oushu_toolkit.recv_tab_data' -- 接收结果数据的关系表
);
select count(*) from oushu_toolkit.recv_tab_data;
select * from oushu_toolkit.recv_tab_data order by level;
批量调用
-- 方法1: 消耗内存
select oushu_toolkit.with_recursive(
array_agg(account_id),
'tbl_while',
'sec_account_id',
'account_id',
'-1',
'0',
'-1',
'oushu_toolkit.recv_tab_data'
)
from tbl_while;
-- 方案2: 需要写代码
for COL in (select xxx from tab)
loop
select oushu_toolkit.with_recursive(
array_agg(COL),
'tbl_while',
'sec_account_id',
'account_id',
'-1',
'0',
'-1',
'oushu_toolkit.recv_tab_data'
) ;
end loop;