----------------------------------------------------------------------------------------------------- 1.查询连接数
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
  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();
----------------------------------------------------------------------------------------------------- d.递归查询函数
-- 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
    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(21, 'dep');
select * from lf.sys_dep order by id;

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;
----------------------------------------------------------------------------------------------------- e.查询用户名
-- 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 uname from lf.sys_user where id = 1 limit 1;
----------------------------------------------------------------------------------------------------- f.递归查询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'));
----------------------------------------------------------------------------------------------------- g.索引
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;

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;
----------------------------------------------------------------------------------------------------- 
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;
----------------------------------------------------------------------------------------------------- 
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';
---------------------------------------------------------------------------------------------- 数据统计 
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 * from lf.sys_user;
select * from lf.sys_menu order by id;