1

13693261870

2023-02-25 79454ce9fbb025d35b7b28144bc9306cf9e546e3

 data/db_fn.sql

@@ -1,71 +1,10 @@
----------------------------------------------------------------------------------------------------- 0.查询连接数
show max_connections;
select count(1) from pg_stat_activity;

select c.relname "tab", cast(obj_description(c.oid) as varchar) "desc", a.attnum "num", a.attname "col",
t.typname "type",concat_ws('', t.typname,SUBSTRING(format_type(a.atttypid, a.atttypmod) from '(.*)')) "type2", d.description "bak"
-- select *
from pg_attribute a left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum left join pg_class c 
on a.attrelid = c.oid left join pg_type t on a.atttypid = t.oid where a.attnum >= 0 and reltype>0 and relnamespace in (29257,20582)--135502,69701
order by c.relname desc, a.attnum asc;

select pg_constraint.conname as pk_name,pg_attribute.attname as colname,pg_type.typname as typename from 
pg_constraint inner join pg_class 
on pg_constraint.conrelid = pg_class.oid 
inner join pg_attribute on pg_attribute.attrelid = pg_class.oid 
and pg_attribute.attnum = pg_constraint.conkey[1]
inner join pg_type on pg_type.oid = pg_attribute.atttypid
where pg_class.relname = 'sys_user' 
and pg_constraint.contype='p';
----------------------------------------------------------------------------------------------------- a.递归查询
--------------------------------------------------------- 查询菜单
select * from lf.sys_menu order by order_num;

with recursive rs as(
  select * from lf.sys_menu where cn_name='管道基础大数据平台'
union
  select a.* from lf.sys_menu a, rs b where a.pid=b.id
)
select * FROM rs order by order_num;
--------------------------------------------------------- 查询单位
select * from lf.sys_dep order by order_num;

with recursive rs as(
  select * from lf.sys_dep where name='中国石油天然气管道工程有限公司'
union
  select a.* from lf.sys_dep a, rs b where a.pid=b.id
)
select * FROM rs order by order_num;
--------------------------------------------------------- 数据目录
select * from lf.sys_dir order by order_num;

with recursive rs as(
  select * from lf.sys_dir where name='中俄东线管道工程南段'
union
  select a.* from lf.sys_dir a, rs b where a.pid=b.id
)
select * FROM rs order by order_num;
----------------------------------------------------------------------------------------------------- b.查询表结构
select * from pg_tables;
select * from pg_class order by relnamespace;
select relnamespace,relkind,relname from pg_class where relnamespace in (select oid from pg_namespace) and relkind='r' order by 1,2;

select c.relname as "表名", cast(obj_description(c.oid) as varchar) as "表名描述",
  a.attnum as "序号", a.attname as "列名", 
  concat_ws('', t.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) from '\(.*\)')) as "字段类型",
  d.description as "备注"
from pg_attribute a
left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
left join pg_class c on a.attrelid = c.oid
left join pg_type t on a.atttypid = t.oid
where a.attnum >= 0 and c.relname like 'sys_%' and obj_description(c.oid) is not null --and relnamespace=20582
order by c.relname desc, a.attnum asc; 
----------------------------------------------------------------------------------------------------- c.GUID生成函数
CREATE or REPLACE FUNCTION new_guid()
RETURNS "pg_catalog"."varchar" AS $BODY$
DECLARE
----------------------------------------------------------------------------------------------------- 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
begin
  select md5(inet_client_addr()::varchar || timeofday() || inet_server_addr()::varchar || to_hex(inet_client_port()))
    into v_seed_value;

@@ -74,18 +13,22 @@
          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;
end;
$body$ language 'plpgsql' volatile security definer;

select new_guid();
----------------------------------------------------------------------------------------------------- d.递归查询函数
-- drop function rec_query_dep(id integer, tab varchar);
select new_guid(); select length(md5('-')); select uuid_generate_v4();
----------------------------------------------------------------------------------------------------- 02.递归查询函数
-- drop function fn_rec_query(integer, 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 '||
@@ -102,16 +45,48 @@
  end;
$$ language plpgsql;

select fn_rec_query(1, 'dep'); select fn_rec_query(21, 'dep');
select * from lf.sys_dep order by id;
select fn_rec_query(1, 'dep'); select fn_rec_query(null, 'dep'); select fn_rec_query(10, 'dir');
----------------------------------------------------------------------------------------------------- 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;

select fn_rec_query(10, 'dir'); select fn_rec_query(28, 'dir');
select * from lf.sys_dir order by id;
      if (char_length(str) > 1) then
           str = substring(str, 2);
      end if;

select a.*, fn_rec_query(a.depid, 'dep') depName from lf.sys_user a order by a.id;
----------------------------------------------------------------------------------------------------- e.查询用户名
      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
@@ -127,10 +102,9 @@
  end;
$$ language plpgsql;

select fn_uname(null);
select uname from lf.sys_user where id = 1 limit 1;
----------------------------------------------------------------------------------------------------- e.查询版本名
-- drop function fn_ver(id integer);
select fn_uname(null); select fn_uname(1);
----------------------------------------------------------------------------------------------------- 05.查询版本名
-- drop function fn_ver(integer);
create or replace function fn_ver(id integer)
returns varchar as $$
  declare
@@ -147,9 +121,8 @@
$$ language plpgsql;

select fn_ver(0);
select a.*,fn_ver(a.verid) ver from lf.sys_meta a;
----------------------------------------------------------------------------------------------------- f.递归查询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
@@ -168,10 +141,331 @@
  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'));
----------------------------------------------------------------------------------------------------- g.获取实体名
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'));
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.获取实体名
-- drop function fn_get_entity(varchar);
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); 
create or replace function fn_tab_count(ns varchar, tab varchar, filters varchar)
returns integer as $$
   declare
      cc  integer;
   begin
      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 = 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
         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
      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);

-- 基于任意两点之间的最短路径分析
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);
/******************************************************************************** 以下为废弃函数 ********************************************************************************/
----------------------------------------------------------------------------------------------------- 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
@@ -189,191 +483,50 @@
   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;
----------------------------------------------------------------------------------------------------- 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 replace(tab, '_', '');
  end;
      return cc;
     end;
$$ language plpgsql;

select fn_get_entity('dlg_25w_boua_s'); select fn_get_entity('dlg_25w_vega'); 
----------------------------------------------------------------------------------------------------- h.索引
alter table lf.sys_role_user add constraint idx_unique_role_user unique (roleid, userid); -- 联合唯一索引
insert into lf.sys_role_user (roleid,userid) values (1,1); -- 测试
select * from lf.sys_role_user;
select fn_alter_tab_fields();

alter table lf.sys_menu_auth add constraint idx_unique_menu_auth unique (menuid, authid); -- 联合唯一索引
insert into lf.sys_menu_auth (menuid, authid) values (1,1); -- 测试
select * from lf.sys_menu_auth;

alter table lf.sys_role_res add constraint idx_unique_role_res unique (roleid,resid); -- 联合唯一索引
insert into lf.sys_role_res (roleid,resid) values (1,1);
select * from lf.sys_role_res;

alter table lf.sys_role_menu_auth add constraint idx_unique_role_menu_auth unique (roleid,menu_auth_id); -- 联合唯一索引
insert into lf.sys_role_menu_auth (roleid,menu_auth_id) values (1,1);
select * from lf.sys_role_menu_auth;
----------------------------------------------------------------------------------------------------- i.联合查询
select a.*,fn_uname(a.create_user) createUserName,fn_uname(a.update_user) updateUserName from lf.sys_blacklist a order by a.id;
select * from lf.sys_user a where uname like '%' and a.depid=ANY(fn_rec_array(1,'dep'));
select * from lf.sys_user a left join lf.sys_role_user b on a.id=b.userid

select a.* from lf.sys_user a where not exists (select b.id from lf.sys_role_user b where b.userid = a.id and b.roleid = 1)
and uname like '%室%' and a.depid = ANY(fn_rec_array(1, 'dep'))
order by a.id limit 10 offset 0;

select a.*,fn_rec_query(a.depid, 'dep') depName,fn_rec_query(a.dirid, 'dir') dirName from lf.sys_style a;
------------------------------------------------------------
select * from lf.sys_auth a left join lf.sys_menu;
select a.* from lf.sys_auth a where not exists (select b.id from lf.sys_menu_auth b where b.authid = a.id and b.menuid = 1);

select a.* from lf.sys_res a where not exists (select b.id from lf.sys_role_res b where b.resid = a.id and b.roleid = 1);

select a.*,c.name from lf.sys_menu_auth a inner join lf.sys_auth c on a.authid = c.id
where not exists (select b.id from lf.sys_role_menu_auth b where b.menu_auth_id = a.id and b.roleid = 1) and a.menuid = 1 order by c.id;

select a.*,b.menuid,c.name from lf.sys_role_menu_auth a inner join lf.sys_menu_auth b on a.menu_auth_id = b.id
inner join lf.sys_auth c on b.authid = c.id where a.roleid = 1 and b.menuid = 1 order by c.id;
-----------------------------------------------------------------------------------------------------
update lf.sys_operate set modular1='运维管理',modular2='菜单管理' where position('/Menu/select' in url)>0 and (modular1 is null or modular2 is null);
update lf.sys_operate set modular1='运维管理',modular2='用户管理' where position('/user/select' in url)>0 and (modular1 is null or modular2 is null);
update lf.sys_operate set modular1='运维管理',modular2='资源管理' where position('/res/select' in url)>0 and (modular1 is null or modular2 is null);

delete from lf.sys_operate where modular1 is null or modular2 is null;
----------------------------------------------------------------------------------------------------- j.查询授权
select gid,gb,name,st_astext(a.geom) from bd.dlg_agnp a;

-- 根据用户Uid查询资源
select distinct d.id,d.name,d.server from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_res c on b.roleid = c.roleid
inner join lf.sys_res d on c.resid = d.id
where a.uid = 'admin';

-- 根据用户Uid查询菜单
select distinct e.id,e.pid,e.cn_name,e.en_name,e.url,e.type,e.icon,e.level,e.order_num,e.is_show
from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
inner join lf.sys_menu e on d.menuid = e.id
where a.uid = 'admin';

-- 根据用户Uid查询权限
select distinct f.id,e.cn_name,f.name,e.perms,f.tag
from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
inner join lf.sys_menu e on d.menuid = e.id
inner join lf.sys_auth f on d.authid = f.id
where a.uid = 'admin';

-- 根据用户Uid查询权限2
select distinct e.perms || f.tag as "perms"
from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
inner join lf.sys_menu e on d.menuid = e.id
inner join lf.sys_auth f on d.authid = f.id
where a.uid = 'admin';
---------------------------------------------------------------------------------------------- k.数据统计 
select modular2,count(*) from lf.sys_operate group by modular2;

select to_char(optime,'yyyy-mm-dd') as optime,count(*) from lf.sys_login where optime between (select optime - interval '30 day')
and optime group by to_char(optime, 'yyyy-mm-dd') order by to_char(optime, 'yyyy-mm-dd') asc;

select a.*,coalesce(a.native,'') from lf.sys_user a;
select ST_PointFromText('POINT(95.80461853400004 34.13862467200005)');
select ST_LineFromText('LINESTRING(04.98985101830993 37.43840773692756,104.99318913447104 37.43883729720358)', 4326);
select ST_PolygonFromText('POLYGON((104.9907822932683 37.43532941961706,104.99088987736599 37.43532941961706,104.9908670336867 37.4349030213574,104.99078327712658 37.4349030213574,104.9907822932683 37.43532941961706))',4326);
---------------------------------------------------------------------------------------------- 
-- 114ms，180/3248
select count(1) from bd.dlg_agnp a where ST_Intersects(geom, 
ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 4326))

-- 1101：gid >= 50 and name like '县' and objectid < 5000.0 and pac = '360430'
select count(*) from bd.dlg_agnp where gid >= 50 and name like '%县%' and objectid < 5000 --and pac = '360430'
and ST_Intersects(geom, 
ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 4326))

select a.* from lf.sys_domain a inner join lf.sys_dict b on a.dom_name = b.domain_na
where b.ns = 'bd' and b.tab = 'dlg_25w_aanp' and b.domain_na is not null;

select * from lf.sys_dict where ns = 'bd' and tab = 'dlg_25w_aanp' and domain_na is not null;
select * from lf.sys_domain where dom_name='dlg25gbcode';
select * from bd.dlg_25w_aanp limit 10;

select * from bd.dlg_25w_agnp
where ST_Intersects(geom, 
ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 0))
limit 10;
select st_srid(geom) from bd.dlg_25w_agnp limit 1;
select * from bd.dlg_25w_agnp where class != 'BB' limit 1;

select a.* from lf.sys_domain a where exists (select * from lf.sys_dict b where b.ns = 'bd' and b.tab = 'dlg_25w_hyda') and a.dom_code like '210%' order by a.id
select * from lf.sys_download a where create_user = 1 and type = 2;
----------------------------------------------------------------------------------------------
select to_char(create_time, 'yyyy-mm-dd hh24:mi:ss') from lf.sys_meta;
select to_timestamp('2018-08-15 12:10:10', 'yyyy-MM-dd hh24:mi:ss');
select a.*,fn_uname(a.create_user) uname from lf.sys_token a
select distinct ns,tab,fn_get_entity(tab) entity,tab_desc,tabletype from lf.sys_dict order by tab;

-- 3248：POINT(95.8046 34.1386)
select * from bd.dlg_agnp;
select count(*) from bd.dlg_agnp;
delete from bd.dlg_agnp;
select ST_AsText(geom) from bd.dlg_agnp limit 1;

-- 7348：MULTILINESTRING((113.6007 6.9434,113.6015 6.9444,113.6007 6.9434))
select * from bd.dlg_25w_boul;
select count(*) from bd.dlg_25w_boul;
delete from bd.dlg_25w_boul;
select ST_AsText(geom) from bd.dlg_25w_boul limit 1;

-- 156847：MULTIPOLYGON(((108.6790 19.0108,108.6791 19.0104,108.6796 19.0104,108.6790 19.0108)))
select * from bd.dlg_25w_resa;
select count(*) from bd.dlg_25w_resa;
delete from bd.dlg_25w_resa;
select ST_AsText(geom) from bd.dlg_25w_resa limit 1;

-- MultiLineString MultiPolygon Point
select GeometryType(geom) from bd.dlg_25w_boul where gid=20;
select st_srid(geom) from bd.dlg_25w_boul where gid=20 limit 1;
select ST_AsText(geom) from bd.dlg_agnp where gid=20;

select * from lf.sys_dict where ns='bd' and tab='dlg_25w_boul' and field='geom';
select * from lf.sys_meta; -- delete from lf.sys_meta where id<113;
select * from lf.sys_meta_file; -- delete from lf.sys_meta_file where metaid<113;

select * from lf.sys_meta_file a where metaid in (113,120,121,122);
alter sequence bd.dlg_agnp_gid_seq restart with 4000;
select max(gid) from  bd.dlg_agnp;
----------------------------------------------------------------------------------------------
select * from bd.dlg_25w_boul where gid<11; -- delete from bd.dlg_25w_boul where gid>10; alter sequence bd.dlg_25w_boul_gid_seq restart with 20;
select * from bd.dlg_25w_resa where gid<12; -- delete from bd.dlg_25w_resa where gid>11; alter sequence bd.dlg_25w_resa_gid_seq restart with 20;
select * from bd.dlg_agnp where gid<13;     -- delete from bd.dlg_agnp where gid>12;     alter sequence bd.dlg_agnp_gid_seq restart with 20;
select gb,name,classes,pinyin,pac,bsm,geom,eventid,dirid,depid,verid,createtime,createuser,updateuser,updatetime from bd.dlg_agnp

select * from bs.bs_project;

select * from bd.dlg_agnp order by gid desc;
insert into bd.dlg_agnp(gb,name) values('01','A01');
insert into bd.dlg_agnp(gb,name) values('02','A02');














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