1

13693261870

2023-02-25 79454ce9fbb025d35b7b28144bc9306cf9e546e3

 data/db_fn.sql

@@ -16,9 +16,9 @@
end;
$body$ language 'plpgsql' volatile security definer;

select new_guid();
select new_guid(); select length(md5('-')); select uuid_generate_v4();
----------------------------------------------------------------------------------------------------- 02.递归查询函数
-- drop function rec_query_dep(id integer, tab varchar);
-- drop function fn_rec_query(integer, varchar);
create or replace function fn_rec_query(id integer, tab varchar)
returns varchar as $$
  declare
@@ -46,9 +46,47 @@
$$ language plpgsql;

select fn_rec_query(1, 'dep'); select fn_rec_query(null, 'dep'); select fn_rec_query(10, 'dir');
----------------------------------------------------------------------------------------------------- 03.查询用户名
----------------------------------------------------------------------------------------------------- 03.递归查询完整名
-- drop function fn_get_fullname(varchar, integer);
create or replace function fn_get_fullname(code varchar, genre integer)
returns varchar as $$
     declare
       rec varchar := '';
       str varchar := '';
      tab varchar := 'dep';
     begin
        if (code is null or genre is null) then
             return '';
       end if;
      if genre = 2 then
         tab := 'dir';
      end if;
		
      for rec in execute 'with recursive rs as (' ||
         'select code, pid, name from lf.sys_' || tab || ' where code = ''' || code || ''' ' ||
         'union select a.code, a.pid, a.name from lf.sys_' || tab || ' a, rs b where a.id = b.pid' ||
         ') select name from rs order by code'
      loop
           str = str || '\' || rec;
      end loop;

      if (char_length(str) > 1) then
           str = substring(str, 2);
      end if;

      return str;
     end;
$$ language plpgsql;

select fn_rec_query(55, 'dep');        select fn_rec_query(90, 'dir');
select fn_get_fullname('00030701', 1); select fn_get_fullname('01000000020000', 2);
/*with recursive rs as (
   select code, pid, name from lf.sys_dep where code = '00030701'
    union select a.code, a.pid, a.name from lf.sys_dep a, rs b where a.id = b.pid
) select name from rs order by code;*/
----------------------------------------------------------------------------------------------------- 04.查询用户名
-- execute format('select uname from lf.sys_user where id = %s', id) into str;
-- drop function fn_uname(id integer);
-- drop function fn_uname(integer);
create or replace function fn_uname(id integer)
returns varchar as $$
  declare
@@ -65,8 +103,8 @@
$$ language plpgsql;

select fn_uname(null); select fn_uname(1);
----------------------------------------------------------------------------------------------------- 04.查询版本名
-- drop function fn_ver(id integer);
----------------------------------------------------------------------------------------------------- 05.查询版本名
-- drop function fn_ver(integer);
create or replace function fn_ver(id integer)
returns varchar as $$
  declare
@@ -83,8 +121,8 @@
$$ language plpgsql;

select fn_ver(0);
----------------------------------------------------------------------------------------------------- 05.递归查询ID数组
-- drop function fn_rec_array(id integer, tab varchar);
----------------------------------------------------------------------------------------------------- 06.递归查询ID数组
-- drop function fn_rec_array(integer, varchar);
create or replace function fn_rec_array(id integer, tab varchar)
returns integer[] as $$
  declare
@@ -105,46 +143,10 @@

select fn_rec_array(1, 'dep'); select fn_rec_array(10, 'dir');
select * from lf.sys_user a where a.depid = ANY(fn_rec_array(15,'dep'));
----------------------------------------------------------------------------------------------------- 06.查询目录ID数组
-- drop function fn_dir_arrs(pids varchar);
create or replace function fn_dir_arrs(pids varchar) returns integer[] as $$
   declare
       ids integer[];
      sid integer;
     begin
       for sid in execute
           'with recursive rs as(select id, pid from lf.sys_dir where id in (' || pids || ') ' ||
           'union select a.id,a.pid from lf.sys_dir a, rs b where a.pid = b.id) ' ||
         'select distinct id from rs order by id'
       loop
            select array_append(ids, sid) into ids;
       end loop;
		
       return ids;
     end;
$$ language plpgsql;

select fn_dir_arrs('2,5,7,9,12');
select * from lf.sys_meta where dirid = ANY(fn_dir_arrs('2,5,7,9,12'));
select * from lf.sys_meta where depcode like '00%'; -- select * from lf.sys_meta where depid = ANY(fn_rec_array(1, 'dep'));
select * from lf.sys_meta where dircode like '01%'; -- select * from lf.sys_meta where dirid = ANY(fn_rec_array(57, 'dir'));
----------------------------------------------------------------------------------------------------- 07.获取实体名
/*create or replace function fn_get_entity(tab varchar)
returns varchar as $$
  declare
   str varchar;
   rs varchar = '';
  begin
   foreach str in array (select string_to_array(tab, '_')) loop
     if (length(rs) = 0 or length(str) = 1) then
       rs = rs || str;
     else
      rs = rs || initcap(str);
     end if;
   end loop;
	
   return rs;
  end;
$$ language plpgsql;*/

-- drop function fn_get_entity(varchar);
create or replace function fn_get_entity(tab varchar)
returns varchar as $$
  declare
@@ -158,63 +160,32 @@
$$ language plpgsql;

select fn_get_entity('dlg_25w_boua_s');
----------------------------------------------------------------------------------------------------- 08.FME日志触发器
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;
	
   return new;
  end;
$$ language plpgsql;

create or replace trigger 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.查询字典表并统计记录
-- drop function fn_tab_count(varchar, varchar, varchar, integer); 
create or replace function fn_tab_count(ns varchar, tab varchar, dirs varchar, depid integer)
----------------------------------------------------------------------------------------------------- 08.查询字典表并统计记录 *
-- drop function fn_tab_count(varchar, varchar, varchar); 
create or replace function fn_tab_count(ns varchar, tab varchar, filters varchar)
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;
      raise notice 'select count(*) from %s.%s where %s', ns, tab, filters;
      execute format('select count(*) from %s.%s where %s', ns, tab, filters) 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'));
----------------------------------------------------------------------------------------------------- 10.10进制转62进制
-- drop function fn_10to62(numeric); 
create or replace function fn_10to62(num numeric(30, 0))
select fn_tab_count('bd', 'dlg_agnp', '1 = 1');
----------------------------------------------------------------------------------------------------- 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
@@ -226,10 +197,10 @@
   end;
$$ language plpgsql;

select fn_10to62(3843);
----------------------------------------------------------------------------------------------------- 11.62进制转10进制
-- drop function fn_62to10(numeric); 
create or replace function fn_62to10(ch varchar)
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);
@@ -245,10 +216,158 @@
   end;
$$ language plpgsql;

select fn_62to10('zz');
----------------------------------------------------------------------------------------------------- 12.路径分析
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
      rid   integer;
      code  varchar;
      cc    integer := 0;
      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 := cc + fn_set_tab_codes(rid, genre);
      end loop;
		
      return cc;
   end;
$$ language plpgsql;

select id, name, code from lf.sys_dir order by id;
select fn_set_tab_codes(1, 1); select fn_set_tab_codes(0, 2);
----------------------------------------------------------------------------------------------------- 13.获取目录编码过滤条件 *
-- drop function fn_get_dir_similar(varchar); 
create or replace function fn_get_dir_similar(name varchar)
returns varchar as $$
   declare
       code varchar;
      str  varchar := '';
   begin
      for code in execute 'select code from lf.sys_dir where name=''' || name || '''' loop
         str := str || '|' || code;
      end loop;
		
      if length(str) > 0 then
         return substr(str, 1);
      end if;
		
      return '1=1';
   end;
$$ language plpgsql;

select * from lf.sys_meta where depcode similar to '(00|01|02)%';
select fn_get_dirs('基础测绘'); select fn_get_dirs('测量（ESV）'); select * from lf.sys_meta where depcode like '0000%';
----------------------------------------------------------------------------------------------------- 14.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;
	
   return new;
  end;
$$ language plpgsql;

-- 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 limit 10;
/*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';*/
----------------------------------------------------------------------------------------------------- 15.单位表触发器
-- 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;*/
----------------------------------------------------------------------------------------------------- 16.目录表触发器
-- drop function fn_dir_insert();
create or replace function fn_dir_insert() returns trigger as $$
  begin
   update lf.sys_dir set code = fn_get_next_code(new.pid, 2) where id = new.id;
	
   return new;
  end;
$$ language plpgsql;

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

select * from lf.sys_dir where pid = 0 order by id;
/*insert into lf.sys_dir (pid,name,level,order_num) values (0,'新目录',1,6);
delete from lf.sys_dir where pid = 0 and id > 309;*/
----------------------------------------------------------------------------------------------------- 17.路径分析
-- 删除已存在的函数
drop function pgr_fromAtoB(varchar, double precision, double precision, double precision, double precision);
-- drop function pgr_fromAtoB(varchar, double precision, double precision, double precision, double precision);

-- 基于任意两点之间的最短路径分析
create or replace function pgr_fromAtoB (
@@ -323,4 +442,91 @@

select ST_astext(ST_Union(geom)) as route from pgr_fromAtoB('lrdl'::text, 116.78999, 39.9468, 116.80458, 39.94758);
select st_astext(geom) route from pgr_fromAtoB('lrdl', 116.78999, 39.9468, 116.80458, 39.94758);
/******************************************************************************** 以下为废弃函数 ********************************************************************************/
----------------------------------------------------------------------------------------------------- a.查询目录ID数组 *
-- drop function fn_dir_arrs(pids varchar);
create or replace function fn_dir_arrs(pids varchar) returns integer[] as $$
   declare
       ids integer[];
      sid integer;
     begin
       for sid in execute
           'with recursive rs as(select id, pid from lf.sys_dir where id in (' || pids || ') ' ||
           'union select a.id,a.pid from lf.sys_dir a, rs b where a.pid = b.id) ' ||
         'select distinct id from rs order by id'
       loop
            select array_append(ids, sid) into ids;
       end loop;
		
       return ids;
     end;
$$ language plpgsql;

select fn_dir_arrs('2,5,7,9,12');
select * from lf.sys_meta where dirid = ANY(fn_dir_arrs('2,5,7,9,12'));
----------------------------------------------------------------------------------------------------- b.获取实体名称
-- drop function fn_get_entity(varchar);
create or replace function fn_get_entity(tab varchar)
returns varchar as $$
  declare
   str varchar;
   rs varchar = '';
  begin
   foreach str in array (select string_to_array(tab, '_')) loop
     if (length(rs) = 0 or length(str) = 1) then
       rs = rs || str;
     else
      rs = rs || initcap(str);
     end if;
   end loop;
	
   return rs;
  end;
$$ language plpgsql;
----------------------------------------------------------------------------------------------------- 09 修改表结构
-- drop function fn_alter_tab_fields();
create or replace function fn_alter_tab_fields()
returns integer as $$
     declare
      rec record;
      tn  varchar;
      cc  integer := 0;
     begin
      for rec in (select ns, tab from lf.sys_dict group by ns, tab order by ns, tab)
      loop
         raise notice 'ns = %, tab = %.', rec.ns, rec.tab;
			
         tn := rec.tab;
         if length(tn) > 32 then
            tn := replace(tn, 'standard_frozen_depth_', 's_f_d_');
            tn := replace(tn, 'general_report_on_', 'g_r_o_');
            tn := replace(tn, 'prevention_and_control_', 'p_a_c_');
            tn := replace(tn, 'water_curtain_roadway_', 'w_c_r_');
         end if;
			
         execute format('alter table %s.%s alter column %s type varchar(24)', rec.ns, rec.tab, 'depid');
         execute format('alter table %s.%s alter column %s type varchar(24)', rec.ns, rec.tab, 'dirid');
			
         execute format('update %s.%s a set depid = (select code from lf.sys_dep b where b.id = cast(a.depid as integer))', rec.ns, rec.tab);
         execute format('update %s.%s a set dirid = (select code from lf.sys_dir b where b.id = cast(a.dirid as integer))', rec.ns, rec.tab);
			
         --execute format('drop index if exists %s.idx_%s_depid', rec.ns, rec.tab);
            execute format('create index idx_%s_depid on %s.%s (depid)', tn, rec.ns, rec.tab);
			
         --execute format('drop index if exists %s.idx_%s_dirid', rec.ns, rec.tab);
            execute format('create index idx_%s_dirid on %s.%s (dirid)', tn, rec.ns, rec.tab);
			
         cc := cc + 1;
      end loop;
	
      return cc;
     end;
$$ language plpgsql;

select fn_alter_tab_fields();

select gid,depid,dirid from bd.b_borehole limit 10;
select gid,depid,dirid from bd.dlg_agnp limit 10;
select gid,depid,dirid from bd.dlg_25w_resa limit 10;
select gid,depid,dirid from bd.dlg_25w_boul limit 10;
-----------------------------------------------------------------------------------------------------