1

13693261870

2022-11-08 16f7d3f0f137c13847ed6bdc7f79cfb288a1e680

 data/db_fn.sql

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

@@ -49,7 +49,6 @@
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 "列名", 
@@ -103,12 +102,10 @@
  end;
$$ language plpgsql;

select fn_rec_query(1, 'dep');
select fn_rec_query(21, 'dep');
select fn_rec_query(1, 'dep'); select fn_rec_query(21, 'dep');
select * from lf.sys_dep order by id;

select fn_rec_query(10, 'dir');
select fn_rec_query(28, 'dir');
select fn_rec_query(10, 'dir'); select fn_rec_query(28, 'dir');
select * from lf.sys_dir order by id;

select a.*, fn_rec_query(a.depid, 'dep') depName from lf.sys_user a order by a.id;
@@ -153,10 +150,29 @@
$$ language plpgsql;

select fn_rec_array(1, 'dep');
select * from lf.sys_user a where a.depid=ANY(fn_rec_array(15,'dep'));

select fn_rec_array(10, 'dir');
----------------------------------------------------------------------------------------------------- g.索引
select * from lf.sys_user a where a.depid=ANY(fn_rec_array(15,'dep'));
----------------------------------------------------------------------------------------------------- g.获取实体名
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;

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;
@@ -172,7 +188,7 @@
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
@@ -183,38 +199,91 @@

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;
select * from lf.sys_menu;
select * from lf.sys_menu_auth;

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 * from lf.sys_res;
select * from lf.sys_role;
select * from lf.sys_role_res;`   `

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 * from lf.sys_role;
select * from lf.sys_menu;
select * from lf.sys_auth;
select * from lf.sys_menu_auth;
select * from lf.sys_role_menu_auth;

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;
----------------------------------------------------------------------------------------------------- 
select * from lf.sys_user;
-----------------------------------------------------------------------------------------------------
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