1

13693261870

2022-10-12 f0fee7d09467aeb0db22f80be0e60fb3e5e6ea84

1

 data/db_fn.sql

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,169 @@
----------------------------------------------------------------------------------------------------- é€’归查询
--------------------------------------------------------- æŸ¥è¯¢èœå•
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;
----------------------------------------------------------------------------------------------------- a.查询连接数
show max_connections;
select count(1) from pg_stat_activity;
----------------------------------------------------------------------------------------------------- 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 * from lf.sys_user a where a.depid=ANY(fn_rec_array(15,'dep'));

select fn_rec_array(10, 'dir');
----------------------------------------------------------------------------------------------------- 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;

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


















 data/db_tab.sql

ÎļþÃû´Ó data/db.sql ÐÞ¸Ä
@@ -18,7 +18,6 @@
  update_time timestamp(6) without time zone,
  bak varchar(1024)
);

comment on table lf.sys_menu is '菜单表';
comment on column lf.sys_menu.id is '主键ID';
comment on column lf.sys_menu.pid is '父ID：0-根节点';
@@ -36,13 +35,8 @@
comment on column lf.sys_menu.update_user is '更新人ID';
comment on column lf.sys_menu.update_time is '更新时间';
comment on column lf.sys_menu.bak is '备注';

--alter table lf.sys_menu alter column bak type varchar(1024);
--alter sequence lf.sys_menu_id_seq restart with 45;
--update lf.sys_menu set bak=null,create_time=now() where 1=1;
delete from lf.sys_menu where id=40;
update lf.sys_menu set cn_name='黑/白名单',en_name='Black/White List' where id=41;
update lf.sys_menu set url='http://192.168.20.39:12316/sign/toDruid?token=',bak='databaseMonitoring' where id=42;

select * from lf.sys_menu order by id;
----------------------------------------------------------------------------------------------------- 2.单位表
@@ -68,7 +62,6 @@
  update_time timestamp(6) without time zone,
  bak varchar(1024)
);

comment on table lf.sys_dep is '单位表';
comment on column lf.sys_dep.id is '主键ID';
comment on column lf.sys_dep.pid is '父ID：0-根节点';
@@ -89,11 +82,8 @@
comment on column lf.sys_dep.update_user is '更新人ID';
comment on column lf.sys_dep.update_time is '更新时间';
comment on column lf.sys_dep.bak is '备注';

--alter sequence lf.sys_dep_id_seq restart with 45;
--alter table lf.sys_dep rename short to sname;
--alter table lf.sys_dep alter column bak type varchar(1024);
--update lf.sys_dep set bak=null,create_time=now() where 1=1;
--alter table lf.sys_dep rename short to sname; alter table lf.sys_dep alter column bak type varchar(1024);

select * from lf.sys_dep order by id;
----------------------------------------------------------------------------------------------------- 3.目录表
@@ -112,7 +102,6 @@
  update_time timestamp(6) without time zone,
  bak varchar(1024)
);

comment on table lf.sys_dir is '目录表';
comment on column lf.sys_dir.id is '主键ID';
comment on column lf.sys_dir.pid is '父ID：0-根节点';
@@ -126,9 +115,7 @@
comment on column lf.sys_dir.update_user is '更新人ID';
comment on column lf.sys_dir.update_time is '更新时间';
comment on column lf.sys_dir.bak is '备注';

--alter sequence lf.sys_dir_id_seq restart with 124;
--update lf.sys_dir set bak=null,create_time=now(),code=id where 1=1;

select * from lf.sys_dir order by id;
----------------------------------------------------------------------------------------------------- 4.用户表
@@ -155,7 +142,6 @@
  update_time timestamp(6) without time zone,
  bak varchar(1024)
);

comment on table lf.sys_user is '用户表';
comment on column lf.sys_user.id is '主键ID';
comment on column lf.sys_user.depid is '单位ID';
@@ -177,8 +163,6 @@
comment on column lf.sys_user.update_user is '更新人ID';
comment on column lf.sys_user.update_time is '更新时间';
comment on column lf.sys_user.bak is '备注';

-- alter table lf.sys_user add unique(uid);
/*insert into lf.sys_user (depid,uid,uname,pwd,create_user) values (1,'admin','管理员','000000',1);
insert into lf.sys_user (depid,uid,uname,pwd,create_user) values (20,'xl','线路室','000000',1);
insert into lf.sys_user (depid,uid,uname,pwd,create_user) values (21,'cky','穿跨越室','000000',1);
@@ -223,13 +207,10 @@
comment on column lf.sys_login.descr is '描述';
comment on column lf.sys_login.userid is '登记人ID';
comment on column lf.sys_login.optime is '登记时间';

/*insert into lf.sys_login (appid,ip,userid) values (1,'192.168.20.106',1);
insert into lf.sys_login (appid,ip,userid,optime) values (1,'192.168.20.106',1,'2024-01-02');*/

select * from lf.sys_login;
select * from lf.sys_login_2022;
select * from lf.sys_login_2024;
select * from lf.sys_login; select * from lf.sys_login_2022; select * from lf.sys_login_2024;
select a.*,b.uname from lf.sys_login a inner join lf.sys_user b on a.userid = b.id;
----------------------------------------------------------------------------------------------------- 6.操作日志表
-- DROP TABLE IF EXISTS lf.sys_operate;
@@ -272,20 +253,15 @@
comment on column lf.sys_operate.userid is '操作人ID';
comment on column lf.sys_operate.optime is '操作时间';
comment on column lf.sys_operate.bak is '备注';

alter table lf.sys_operate add column modular1 varchar(50);
alter table lf.sys_operate add column modular2 varchar(50);
alter table lf.sys_operate drop column modular;
select split_part(modular,'\',1),split_part(modular,'\',2) from lf.sys_operate;
update lf.sys_operate set modular1=split_part(modular,'\',1),modular2=split_part(modular,'\',2);
delete from lf.sys_operate where modular is null and modular2 is null;
/*insert into lf.sys_operate (modular,url,ip,type) values ('数据管理\目录管理','data/manage/update','192.168.20.106',5);
insert into lf.sys_operate (modular,url,ip,type,optime) values ('数据管理\目录管理','data/manage/delete','192.168.20.107',4,'2024-01-02');*/

alter table lf.sys_operate add column modular1 varchar(50); alter table lf.sys_operate drop column modular;
select split_part(modular,'\',1),split_part(modular,'\',2) from lf.sys_operate;
update lf.sys_operate set modular1=split_part(modular,'\',1),modular2=split_part(modular,'\',2);

select a.*,b.uname from lf.sys_operate a inner join lf.sys_user b on a.userid = b.id;
select * from lf.sys_operate;
select * from lf.sys_operate_2022;
select * from lf.sys_operate_2024;
select * from lf.sys_operate; select * from lf.sys_operate_2022; select * from lf.sys_operate_2024;
----------------------------------------------------------------------------------------------------- 7.令牌表
-- DROP TABLE IF EXISTS lf.sys_token;
create table lf.sys_token(
@@ -325,17 +301,13 @@
comment on column lf.sys_token.create_time is '创建时间';
comment on column lf.sys_token.update_user is '更新人ID';
comment on column lf.sys_token.update_time is '更新时间';

--select now()::timestamp + '1 year 4 hour';
/*insert into lf.sys_token (token,ip) values (new_guid(),'192.168.20.106');
insert into lf.sys_token (token,ip,expire,create_time) values (new_guid(),'192.168.20.107',now()::timestamp + '1 year 4 hour',now()::timestamp + '1 year');*/

select * from lf.sys_token where token='91cf74dc-1d03-4937-983f-88810589b112' and expire > now() limit 1;
select * from lf.sys_user where id=(select create_user from lf.sys_token where token='91cf74dc-1d03-4937-983f-88810589b112' and expire > now() limit 1);

select * from lf.sys_token;
select * from lf.sys_token_2022;
select * from lf.sys_token_2023;
select * from lf.sys_token; select * from lf.sys_token_2022; select * from lf.sys_token_2023; select now()::timestamp + '1 year 4 hour';
----------------------------------------------------------------------------------------------------- 8.资源表
-- DROP TABLE IF EXISTS lf.sys_res;
create table lf.sys_res(
@@ -354,7 +326,6 @@
  update_time timestamp(6) without time zone,
  bak varchar(1024)
);

comment on table lf.sys_res is '资源表';
comment on column lf.sys_res.id is '主键ID';
comment on column lf.sys_res.name is '名称';
@@ -370,7 +341,6 @@
comment on column lf.sys_res.update_user is '更新人ID';
comment on column lf.sys_res.update_time is '更新时间';
comment on column lf.sys_res.bak is '备注';

--insert into lf.sys_res (name) values ('天地图影像');

select * from lf.sys_res;
@@ -405,16 +375,13 @@
comment on column lf.sys_res_op.ip is 'IP地址';
comment on column lf.sys_res_op.userid is '操作人ID';
comment on column lf.sys_res_op.optime is '创建时间';

/*insert into lf.sys_res_op (resid,ip,type) values (1,'192.168.20.106',5);
insert into lf.sys_res_op (resid,ip,type) values (1,'192.168.20.106',1);
insert into lf.sys_res_op (resid,ip,type,optime) values (1,'192.168.20.107',4,'2024-01-02');*/

select a.*,b.uname,c.name from lf.sys_res_op a inner join lf.sys_user b on a.userid = b.id inner join lf.sys_res c on a.resid=c.id;

select * from lf.sys_res_op;
select * from lf.sys_res_op_2022;
select * from lf.sys_res_op_2024;
select * from lf.sys_res_op; select * from lf.sys_res_op_2022; select * from lf.sys_res_op_2024;
----------------------------------------------------------------------------------------------------- 10.权限表
-- DROP TABLE IF EXISTS lf.sys_auth;
create table lf.sys_auth(
@@ -427,7 +394,6 @@
  update_time timestamp(6) without time zone,
  bak varchar(1024)
);

comment on table lf.sys_auth is '权限表';
comment on column lf.sys_auth.id is '主键ID';
comment on column lf.sys_auth.name is '名称';
@@ -437,7 +403,6 @@
comment on column lf.sys_auth.update_user is '更新人ID';
comment on column lf.sys_auth.update_time is '更新时间';
comment on column lf.sys_auth.bak is '备注';

/*insert into lf.sys_auth (name,tag) values ('查看','select');
insert into lf.sys_auth (name,tag) values ('新增','insert');
insert into lf.sys_auth (name,tag) values ('修改','update');
@@ -459,7 +424,6 @@
  update_time timestamp(6) without time zone,
  bak varchar(1024)
);

comment on table lf.sys_role is '角色表';
comment on column lf.sys_role.id is '主键ID';
comment on column lf.sys_role.depid is '单位ID';
@@ -470,15 +434,10 @@
comment on column lf.sys_role.update_user is '更新人ID';
comment on column lf.sys_role.update_time is '更新时间';
comment on column lf.sys_role.bak is '备注';

--insert into lf.sys_role (depid,name,descr) values (1,'Admin','系统管理员');

select * from lf.sys_role;

select a.*,b.name depname
from lf.sys_role a inner join lf.sys_dep b
on a.depid = b.id
order by a.id limit 10 offset 0;
select a.*,b.name depname from lf.sys_role a inner join lf.sys_dep b on a.depid = b.id order by a.id limit 10 offset 0;
----------------------------------------------------------------------------------------------------- 12.角色-用户表
-- DROP TABLE IF EXISTS lf.sys_role_user;
create table lf.sys_role_user(
@@ -490,7 +449,6 @@
  update_user integer,
  update_time timestamp(6) without time zone
);

comment on table lf.sys_role_user is '角色-用户表';
comment on column lf.sys_role_user.id is '主键ID';
comment on column lf.sys_role_user.roleid is '角色ID';
@@ -499,7 +457,6 @@
comment on column lf.sys_role_user.create_time is '创建时间';
comment on column lf.sys_role_user.update_user is '更新人ID';
comment on column lf.sys_role_user.update_time is '更新时间';

--insert into lf.sys_role_user (roleid,userid) values (1,1);

select * from lf.sys_role_user;
@@ -514,7 +471,6 @@
  update_user integer,
  update_time timestamp(6) without time zone
);

comment on table lf.sys_role_res is '角色-资源表';
comment on column lf.sys_role_res.id is '主键ID';
comment on column lf.sys_role_res.roleid is '角色ID';
@@ -523,7 +479,6 @@
comment on column lf.sys_role_res.create_time is '创建时间';
comment on column lf.sys_role_res.update_user is '更新人ID';
comment on column lf.sys_role_res.update_time is '更新时间';

--insert into lf.sys_role_res (roleid,resid) values (1,1);

select * from lf.sys_role_res;
@@ -538,7 +493,6 @@
  update_user integer,
  update_time timestamp(6) without time zone
);

comment on table lf.sys_menu_auth is '菜单-权限表';
comment on column lf.sys_menu_auth.id is '主键ID';
comment on column lf.sys_menu_auth.menuid is '菜单ID';
@@ -547,7 +501,6 @@
comment on column lf.sys_menu_auth.create_time is '创建时间';
comment on column lf.sys_menu_auth.update_user is '更新人ID';
comment on column lf.sys_menu_auth.update_time is '更新时间';

--insert into lf.sys_menu_auth (menuid,authid) values (1,1);

select * from lf.sys_menu_auth;
@@ -562,7 +515,6 @@
  update_user integer,
  update_time timestamp(6) without time zone
);

comment on table lf.sys_role_menu_auth is '角色-菜单表';
comment on column lf.sys_role_menu_auth.id is '主键ID';
comment on column lf.sys_role_menu_auth.roleid is '角色ID';
@@ -571,7 +523,6 @@
comment on column lf.sys_role_menu_auth.create_time is '创建时间';
comment on column lf.sys_role_menu_auth.update_user is '更新人ID';
comment on column lf.sys_role_menu_auth.update_time is '更新时间';

--insert into lf.sys_role_menu_auth (roleid,menu_auth_id) values (1,1);

select * from lf.sys_role_menu_auth;
@@ -600,7 +551,6 @@
  CONSTRAINT enforce_srid_geom CHECK (st_srid(geo) = 4490)
);
create index index_sys_meta_geo on lf.sys_meta using gist (geo);

comment on table lf.sys_meta is '元数据表';
comment on column lf.sys_meta.id is '主键ID';
comment on column lf.sys_meta.dirid is '目录ID';
@@ -619,11 +569,9 @@
comment on column lf.sys_meta.update_user is '更新人ID';
comment on column lf.sys_meta.update_time is '更新时间';
comment on column lf.sys_meta.geo is '空间';

--insert into lf.sys_meta(dirid,depid,name,geo) values (1,1,'Test',ST_PolygonFromText('POLYGON ((100 0,120 0,120 20,100 20,100 0))',4490));

select id,dirid,depid,name,descr,guid,create_user,create_time,update_user,update_time,st_astext(geo) from lf.sys_meta;
select * from lf.sys_meta;
----------------------------------------------------------------------------------------------------- 17.元数据文件表
-- DROP TABLE IF EXISTS lf.sys_meta_file;
create table lf.sys_meta_file(
@@ -638,7 +586,6 @@
  update_user integer,
  update_time timestamp(6) without time zone
);

comment on table lf.sys_meta_file is '元数据文件表';
comment on column lf.sys_meta_file.id is '主键ID';
comment on column lf.sys_meta_file.metaid is '元数据ID';
@@ -650,7 +597,6 @@
comment on column lf.sys_meta_file.create_time is '创建时间';
comment on column lf.sys_meta_file.update_user is '更新人ID';
comment on column lf.sys_meta_file.update_time is '更新时间';

--insert into lf.sys_meta_file (metaid,name,path) values (1,'a.jpg','c:\a.jpg');

select * from lf.sys_meta_file;
@@ -667,7 +613,6 @@
  update_user integer,
  update_time timestamp(6) without time zone
);

comment on table lf.sys_attach is '附件表';
comment on column lf.sys_attach.id is '主键ID';
comment on column lf.sys_attach.name is '文件名';
@@ -678,11 +623,9 @@
comment on column lf.sys_attach.create_time is '创建时间';
comment on column lf.sys_attach.update_user is '更新人ID';
comment on column lf.sys_attach.update_time is '更新时间';

--insert into lf.sys_attach (name,tab,path) values ('a.jpg','public.data_dir','c:\a.jpg');

select length(guid) from lf.sys_attach limit 1;
select * from lf.sys_attach;
select length(guid) from lf.sys_attach limit 1; select * from lf.sys_attach;
----------------------------------------------------------------------------------------------------- 19.版本表
-- DROP TABLE IF EXISTS lf.sys_ver;
create table lf.sys_ver(
@@ -695,7 +638,6 @@
  update_user integer,
  update_time timestamp(6) without time zone
);

comment on table lf.sys_ver is '附件表';
comment on column lf.sys_ver.id is '主键ID';
comment on column lf.sys_ver.dirid is '目录ID';
@@ -705,7 +647,6 @@
comment on column lf.sys_ver.create_time is '创建时间';
comment on column lf.sys_ver.update_user is '更新人ID';
comment on column lf.sys_ver.update_time is '更新时间';

--insert into lf.sys_ver (dirid,name) values (1003,'2020-02-01');

select * from lf.sys_ver;
@@ -728,7 +669,6 @@
  update_time timestamp(6) without time zone,
  bak varchar(1024) 
);

comment on table lf.sys_dict is '附件表';
comment on column lf.sys_dict.id is '主键ID';
comment on column lf.sys_dict.ns is '表空间';
@@ -745,7 +685,6 @@
comment on column lf.sys_dict.update_user is '更新人ID';
comment on column lf.sys_dict.update_time is '更新时间';
comment on column lf.sys_dict.bak is '备注';

/* alter sequence lf.sys_dict_id_seq restart with 300;
update lf.sys_dict set create_user=1,create_time=now() where 1=1;
update lf.sys_dict set bak=null where bak=' '; */
@@ -772,7 +711,6 @@
  update_time timestamp(6) without time zone,
  bak varchar(1024)
);

comment on table lf.sys_style is '样式表';
comment on column lf.sys_style.id is '主键ID';
comment on column lf.sys_style.name is '名称';
@@ -790,7 +728,6 @@
comment on column lf.sys_style.update_user is '更新人ID';
comment on column lf.sys_style.update_time is '更新时间';
comment on column lf.sys_style.bak is '备注';

--insert into lf.sys_style (name,type,precision,descr) values ('line.edp','edp','1:1000','管道中心线样式文件');

select count(*) from lf.sys_style where name like '%.edp';
@@ -818,7 +755,6 @@
  CONSTRAINT enforce_srid_geom CHECK (st_srid(geo) = 4490)
);
create index index_sys_download_geo on lf.sys_download using gist (geo);

comment on table lf.sys_download is '下载记录表';
comment on column lf.sys_download.id is '主键ID';
comment on column lf.sys_download.name is '名称';
@@ -835,7 +771,6 @@
comment on column lf.sys_download.download_time is '下载时间';
comment on column lf.sys_download.geo is '空间';
comment on column lf.sys_download.bak is '备注';

-- insert into lf.sys_download (name,pwd,url,descr) values ('2022-09-27','123456','','测试下载');

select * from lf.sys_download;
@@ -851,7 +786,6 @@
  update_user integer,
  update_time timestamp(6) without time zone
);

comment on table lf.sys_msg is '消息通知表';
comment on column lf.sys_msg.id is '主键ID';
comment on column lf.sys_msg.title is '标题';
@@ -861,7 +795,6 @@
comment on column lf.sys_msg.create_time is '创建时间';
comment on column lf.sys_msg.update_user is '接受人ID';
comment on column lf.sys_msg.update_time is '接受时间';

--insert into lf.sys_msg (title,msg,guid,update_user) values ('2022-09-26','数据下载',new_guid(),2);

select * from lf.sys_msg;
@@ -880,7 +813,6 @@
  update_user integer,
  update_time timestamp(6) without time zone
);

comment on table lf.sys_args is '参数设置表';
comment on column lf.sys_args.id is '主键ID';
comment on column lf.sys_args.name is '名称';
@@ -893,7 +825,6 @@
comment on column lf.sys_args.create_time is '创建时间';
comment on column lf.sys_args.update_user is '更新人ID';
comment on column lf.sys_args.update_time is '更新时间';

/* insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,descr) values ('自动登出时间',15,15,3,1440,'默认用户15分钟不操作，就自动登出系统。');
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,descr) values ('令牌有效期',240,240,60,1440,'临时令牌默认有效期为240分钟（4小时），超时将失效。');
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,descr) values ('缓存有效期',240,240,60,1440,'缓存默认有效期为1440分钟（1天），超时将失效。'); */
@@ -912,7 +843,6 @@
  update_user integer,
  update_time timestamp(6) without time zone
);

comment on table lf.sys_blacklist is '黑名单表';
comment on column lf.sys_blacklist.id is '主键ID';
comment on column lf.sys_blacklist.ip is 'IP地址';
@@ -923,152 +853,8 @@
comment on column lf.sys_blacklist.create_time is '创建时间';
comment on column lf.sys_blacklist.update_user is '更新人ID';
comment on column lf.sys_blacklist.update_time is '更新时间';

/* insert into lf.sys_blacklist (ip,type) values ('192.168.20.99',1);
insert into lf.sys_blacklist (ip,type) values ('127.0.0.1',2); */

select * from lf.sys_blacklist;
----------------------------------------------------------------------------------------------------- é€’归查询
--------------------------------------------------------- æŸ¥è¯¢èœå•
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;
----------------------------------------------------------------------------------------------------- a.查询连接数
show max_connections;
select count(1) from pg_stat_activity;
----------------------------------------------------------------------------------------------------- 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 * from lf.sys_user a where a.depid=ANY(fn_rec_array(15,'dep'));

select fn_rec_array(10, 'dir');
----------------------------------------------------------------------------------------------------- 
select a.*,fn_uname(a.create_user) createUserName,fn_uname(a.update_user) updateUserName from lf.sys_blacklist a order by a.id;
-----------------------------------------------------------------------------------------------------