1

13693261870

2023-02-13 06492d128df5d7d0905539dd04faa9d475e9de59

 data/db_fn.sql

@@ -158,7 +158,157 @@
$$ language plpgsql;

select fn_get_entity('dlg_25w_boua_s');
----------------------------------------------------------------------------------------------------- 08.FME日志触发器 *
----------------------------------------------------------------------------------------------------- 08.查询字典表并统计记录
-- drop function fn_tab_count(varchar, varchar, varchar, integer); 
create or replace function fn_tab_count(ns varchar, tab varchar, dirs varchar, depid integer)
returns integer as $$
   declare
      sql varchar;
      cc  integer;
   begin
      sql := 'select count(*) from ' || ns || '.' || tab || ' where 1 = 1';
		
      if (dirs is not null) then
         sql := sql || ' and dirid = ANY(fn_dir_arrs(''' || dirs ||  '''))';
      end if;
		
      if (depid is not null) then
         sql := sql || ' and depid = ANY(fn_rec_array(' || depid || ', ''dep''))';
      end if;
		
      execute sql into cc;
		
      return cc;
   end;
$$ language plpgsql;

select fn_tab_count('bd', 'dlg_agnp', '1,57', 1);
-- select count(*) from bd.dlg_agnp where 1=1 and dirid = ANY(fn_dir_arrs('1,57')) and depid = ANY(fn_rec_array(1, 'dep'));
select ns,tab,tab_desc,fn_get_entity(tab) entity,fn_tab_count(ns, tab, '1,57', 1) "len" from lf.sys_dict where tab_desc like '%' group by ns,tab,tab_desc limit 10;

with rs as (select ns,tab,tab_desc,fn_get_entity(tab) entity from lf.sys_dict where tab_desc like '%' group by ns,tab,tab_desc limit 10)
select rs.*,fn_tab_count(rs.ns, rs.tab, null, 1) "len" from rs;

select fn_rec_array(1, 'dep')
select count(*) from lf.sys_meta where depid = ANY(fn_rec_array(1, 'dep'));
select count(*) from bd.dlg_25w_lrdl where depid = ANY(fn_rec_array(1, 'dep'));
----------------------------------------------------------------------------------------------------- 09.10进制转62进制
-- drop function fn_10_to_62(numeric);
create or replace function fn_10_to_62(num numeric(30, 0))
returns varchar as $$
   declare
      rs varchar;
   begin
      if (num = 0) then
         return '0';
      end if;
	
      WITH RECURSIVE T(N, S) AS (
         SELECT num N, '' S
         UNION ALL
         SELECT trunc(N / 62)::NUMERIC(30, 0), substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', (N % 62)::INT + 1, 1) || S FROM T WHERE N > 0 
      ) 
      SELECT S INTO rs FROM T WHERE N = 0;
		
      return rs;
   end;
$$ language plpgsql;

select fn_10_to_62(3843); select fn_10_to_62(0); select fn_10_to_62(3844);
----------------------------------------------------------------------------------------------------- 10.62进制转10进制
-- drop function fn_62_to_10(varchar); 
create or replace function fn_62_to_10(ch varchar)
returns numeric as $$
   declare
      rs numeric(30, 0);
   begin
      WITH RECURSIVE T(S, N) AS (
         SELECT ch S, 0::NUMERIC N
         UNION ALL
         SELECT SUBSTR(S, 2), (POWER(62, LENGTH(S)::NUMERIC - 1) * (strpos('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', SUBSTR(S, 1, 1)) - 1) + N) FROM T WHERE LENGTH(S) > 0
      )
      SELECT N INTO rs FROM T WHERE LENGTH(S) < 1;
		
      return rs;
   end;
$$ language plpgsql;

select fn_62_to_10('zz'); select fn_62_to_10('0'); select fn_62_to_10('100');
----------------------------------------------------------------------------------------------------- 11.获取下一个编码
-- drop function fn_get_next_code(integer, integer); 
create or replace function fn_get_next_code(pid integer, genre integer)
returns varchar as $$
   declare
       cc    integer;
      code  varchar;
      pcode varchar;
      tab   varchar := 'dep';
   begin
      if (pid is null or genre is null) then
             return '00';
       end if;
      if genre = 2 then
         tab := 'dir';
      end if;
		
      execute 'select code from lf.sys_' || tab || ' where id = ' || pid into pcode;
      if pcode is null then
         pcode := '';
      end if;
		
      for ii in 0..3843 loop
         code := fn_10_to_62(ii);
         if length(code) = 1 then
            code := '0' || code;
         end if;
		
         execute 'select count(*) from lf.sys_' || tab || ' where pid = ' || pid || ' and code = ''' || pcode || code || '''' into cc;
         if cc = 0 then
            return pcode || code;
         end if;
      end loop;
		
      return pcode || '00';
   end;
$$ language plpgsql;

select fn_get_next_code(0, 1); select fn_get_next_code(0, 2);
----------------------------------------------------------------------------------------------------- 12.设置表的编码：1-单位，2-目录
-- drop function fn_set_tab_codes(integer, integer); 
create or replace function fn_set_tab_codes(pid integer, genre integer)
returns integer as $$
   declare
      cc    integer;
      rid   integer;
      code  varchar;
      tab   varchar := 'dep';
   begin
      if (pid is null or genre is null) then
             return 0;
       end if;
      if genre = 2 then
         tab := 'dir';
      end if;
		
      execute 'select count(*) from lf.sys_' || tab || ' where pid = ' || pid into cc;
      if cc = 0 then
         return 0;
      end if;
		
      for rid in execute 'select id from lf.sys_' || tab || ' where code is null and pid = ' || pid || ' order by order_num' loop
         code := fn_get_next_code(pid, genre);
         execute 'update lf.sys_' || tab || ' set code = ''' || code || ''' where id = ' || rid;
			
         cc := fn_set_tab_codes(rid, genre);
      end loop;
		
      return 1;
   end;
$$ language plpgsql;

select fn_set_tab_codes(1, 1); select fn_set_tab_codes(0, 2);
----------------------------------------------------------------------------------------------------- 13.FME日志表触发器
-- drop function fn_meta_insert();
create or replace function fn_meta_insert() returns trigger as $$
  begin
    update lf.sys_meta set tab = new.pg_ns || '.' || new.tcdm, layer = new.tcmc, rows = new.count where eventid = new.parentid;
@@ -167,14 +317,35 @@
  end;
$$ language plpgsql;

create or replace trigger fme_log_trigger after insert on lf.sys_fme_log
-- drop trigger fme_log_trigger on lf.sys_fme_log;
create or replace trigger fn_fme_log_trigger after insert on lf.sys_fme_log
for each row execute procedure fn_meta_insert();

select * from lf.sys_meta;
/*insert into lf.sys_fme_log (parentid,dirpath,pg_ns,tcmc,tcdm,count) values
  ('8fb3c3dd-6a12-488d-80ae-a93cc7e8b2e3','','bs','中线成果表','m_pipelinepoint',0);
select * from bs.m_pipelinepoint where parentid='8fb3c3dd-6a12-488d-80ae-a93cc7e8b2e3';*/
----------------------------------------------------------------------------------------------------- 09.路径分析
----------------------------------------------------------------------------------------------------- 14.单位表触发器
-- drop function fn_dep_insert();
create or replace function fn_dep_insert() returns trigger as $$
  begin
   update lf.sys_dep set code = fn_get_next_code(new.pid, 1) where id = new.id;
	
   return new;
  end;
$$ language plpgsql;

-- drop trigger fn_dep_trigger on lf.sys_dep;
create or replace trigger fn_dep_trigger after insert on lf.sys_dep
for each row execute procedure fn_dep_insert();

select * from lf.sys_dep where pid = 0 order by id;
/*insert into lf.sys_dep (pid,name,level,order_num) values (0,'新单位',1,2);
delete from lf.sys_dep where pid = 0 and id > 1;*/
----------------------------------------------------------------------------------------------------- 15.目录表触发器


----------------------------------------------------------------------------------------------------- 16.路径分析
-- 删除已存在的函数
drop function pgr_fromAtoB(varchar, double precision, double precision, double precision, double precision);