[postgresql]postgresql自定义函数查询ETL作业依赖的实例

---------------------------------------------------
---需求：查找ETL依赖关系
---描述：根据作业ID，查找上游依赖的作业ID
---依赖表结构：TBL_METADATA_RELATION：
               --id int
               --sourceid varchar2
               --targetid varchar2
--原理：对树结构进行深度遍历，使用栈的保存中间结果
--------------------------------------------------
SET serveroutput ON;
--创建类型
create or replace type id_list_table is table of varchar2(36);
/
--创建函数
drop function  p_search_source; 
CREATE OR REPLACE  function p_search_source( id  VARCHAR2) 
return id_list_table
IS
    v_table_temp id_list_table;
    v_table_stack id_list_table;
    v_table_source id_list_table; 
    i int;
    j int;
 BEGIN 
  v_table_temp:=id_list_table();
  v_table_stack:=id_list_table();
  v_table_source:=id_list_table();
  
  select SOURCE_ID bulk collect into v_table_temp from idata.TBL_METADATA_RELATION where target_id=id;
  v_table_stack:=v_table_temp;
  i:=v_table_stack.count;
  dbe_output.print_line('[info] start i='|| i );
  dbe_output.print_line('[info] v_table_stack count:'|| v_table_stack.count );
  
  if v_table_stack.count<>0
  then  

    while i>0 loop
       
       dbe_output.print_line('[info] '|| v_table_stack(i) ||' add to v_table_source');
       v_table_source.extend(1);
       v_table_source(v_table_source.count):=v_table_stack(i);
       --初始化为空数据集
       v_table_temp:=id_list_table();
       select SOURCE_ID bulk collect into v_table_temp from idata.TBL_METADATA_RELATION where target_id=v_table_stack(i);

    --栈顶元素出栈
       v_table_stack.trim;
       j:=v_table_temp.count;
       while j>0 loop
          v_table_stack.extend(1);
          v_table_stack(v_table_stack.count):=v_table_temp(j);

          j:=j-1;
       end loop; 
      i:=v_table_stack.count;    
      dbe_output.print_line('[info] v_table_source.count='||v_table_source.count );
    end loop;
  end if;
   return v_table_source;
END;
/
--执行
select COLUMN_VALUE as sources from table(cast(p_search_source('d6531654-f6ed-46dd-ac81-4efd9bf4afa4') as id_list_table ));