----------------------------------------------------------------------------------------------------- 01.GUID生成函数
-- drop function new_guid();
create or replace function new_guid()
  returns "pg_catalog"."varchar" as $body$
declare
  v_seed_value varchar(32);
begin
  select md5(inet_client_addr()::varchar || timeofday() || inet_server_addr()::varchar || to_hex(inet_client_port()))
    into v_seed_value;

  return (substr(v_seed_value,1,8) || '-' ||
          substr(v_seed_value,9,4) || '-' ||
          substr(v_seed_value,13,4) || '-' ||
          substr(v_seed_value,17,4) || '-' ||
          substr(v_seed_value,21,12));
end;
$body$ language 'plpgsql' volatile security definer;

select new_guid();
----------------------------------------------------------------------------------------------------- 02.递归查询函数
-- drop function rec_query_dep(id integer, tab varchar);
create or replace function fn_rec_query(id integer, tab varchar)
returns varchar as $$
  declare
    str varchar = '';
    rec varchar = '';
  begin
    if (id is null or tab is null) then
      return '';
	end if;
	
    for rec in execute 'with recursive rs as(' || 
      'select id,pid,name from lf.sys_' || tab || ' where id=' || id ||
	  ' union select a.id,a.pid,a.name from lf.sys_' || tab || ' a, rs b where a.id=b.pid '||
	  ') select name from rs order by id'
    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(1, 'dep'); select fn_rec_query(null, 'dep'); select fn_rec_query(10, 'dir');
----------------------------------------------------------------------------------------------------- 03.查询用户名
-- execute format('select uname from lf.sys_user where id = %s', id) into str;
-- drop function fn_uname(id integer);
create or replace function fn_uname(id integer)
returns varchar as $$
  declare
    str varchar;
  begin
  	if (id is null) then
		return null;
	end if;
  
	execute 'select uname from lf.sys_user where id = ' || id into str;
	
    return str;
  end;
$$ language plpgsql;

select fn_uname(null); select fn_uname(1);
----------------------------------------------------------------------------------------------------- 04.查询版本名
-- drop function fn_ver(id integer);
create or replace function fn_ver(id integer)
returns varchar as $$
  declare
    str varchar;
  begin
  	if (id is null) then
		return null;
	end if;
  
	execute 'select name from lf.sys_ver where id = ' || id into str;
	
    return str;
  end;
$$ language plpgsql;

select fn_ver(0);
----------------------------------------------------------------------------------------------------- 05.递归查询ID数组
-- drop function fn_rec_array(id integer, tab varchar);
create or replace function fn_rec_array(id integer, tab varchar)
returns integer[] as $$
  declare
    ids integer[];
	sid integer;
  begin
    for sid in execute 'with recursive rs as(' || 
      'select id,pid from lf.sys_' || tab || ' where id=' || id ||
	  ' union select a.id,a.pid from lf.sys_' || tab || ' a, rs b where a.pid=b.id '||
	  ') select id from rs order by id'
    loop
      select array_append(ids, sid) into ids;
    end loop;
	
    return ids;
  end;
$$ language plpgsql;

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'));
----------------------------------------------------------------------------------------------------- 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;*/

create or replace function fn_get_entity(tab varchar)
returns varchar as $$
  declare
  begin
	if (tab is null) then
	  return '';
	end if;
	
	return replace(tab, '_', '');
  end;
$$ language plpgsql;

select fn_get_entity('dlg_25w_boua_s');
----------------------------------------------------------------------------------------------------- 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;
	
	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;
/*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';*/
----------------------------------------------------------------------------------------------------- 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);

-- 基于任意两点之间的最短路径分析
create or replace function pgr_fromAtoB (
    in tbl varchar,            -- 数据库表名
    in x1 double precision,    -- 起点x坐标
    in y1 double precision,    -- 起点y坐标
    in x2 double precision,    -- 终点x坐标
    in y2 double precision,    -- 终点y坐标
    out seq integer,           -- 道路序号
    out gid integer,
    out name text,             -- 道路名
    out heading double precision,
    out cost double precision, -- 消耗
    out geom geometry          -- 道路几何集合
) returns setof record as $body$
declare
	sql     text;
	rec     record;
	source    integer;
	target    integer;
	point    integer;
begin
-- 查询距离出发点最近的道路节点
execute 'select id::integer from ' || quote_ident(tbl)
	|| '_vertices_pgr order by the_geom <-> st_geometryfromtext(''point('
	|| x1 || ' ' || y1 || ')'',4490) limit 1' into rec;
source := rec.id;

-- 查询距离目的地最近的道路节点
execute 'select id::integer from ' || quote_ident(tbl)
	|| '_vertices_pgr order by the_geom <-> st_geometryfromtext(''point('
	|| x2 || ' ' || y2 || ')'',4490) limit 1' into rec;
target := rec.id;

-- 最短路径查询
seq := 0;
sql := 'select gid, geom, node as name, cost, source, target, st_reverse(geom) as flip_geom from '
	|| 'pgr_dijkstra(''select gid as id,source::integer,target::integer,'
	|| 'length::float as cost from '
	|| quote_ident(tbl) || ''', '
	|| source || ', ' || target
	|| ' ,false) as di, '
	|| quote_ident(tbl) || ' where di.edge = gid order by seq';

-- remember start point
point := source;
for rec in execute sql
	loop
		-- flip geometry (if required)
		if ( point != rec.source ) then
			rec.geom := rec.flip_geom;
			point    := rec.source;
		else
			point    := rec.target;
		end if;

		-- calculate heading (simplified)
		execute 'select degrees( st_azimuth(st_startpoint(''' || rec.geom::text
			|| '''),st_endpoint(''' || rec.geom::text || ''') ) )' into heading;

		-- return record
		seq  := seq + 1;
		gid  := rec.gid;
		name := rec.name;
		cost := rec.cost;
		geom := rec.geom;
		return next;
	end loop;
return;
end;
$body$ language 'plpgsql' volatile strict;

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);
-----------------------------------------------------------------------------------------------------