1

13693261870

2023-02-25 79454ce9fbb025d35b7b28144bc9306cf9e546e3

 data/db_cx.sql

@@ -6,14 +6,44 @@
create extension fuzzystrmatch;
create extension postgis_tiger_geocoder;
create extension address_standardizer;
create extension "uuid-ossp";

select uuid_generate_v4();                    -- uuid，36位
select split_part('12.34.56.78', '.', 1);     -- 切割
select substr('FY2021', 3, 2);                -- 提取
select substr('FY2021', 3);                   -- 提取
select substring('FY2021' FROM 3 for 2);      -- 提取
select position('23' in 'w123456');           -- 位置
select md5('');                               -- MD5
select concat('aa', 'bb');                    -- 连接
select concat_ws('_', 'a', 'b');              -- 连接
select chr(97);                               -- ACSII值转字符
select ascii('x');                            -- 字符转ACSII值
select to_hex(31);                            -- 转16进制
select length('aabbcc');                      -- 字符长度
select reverse('aabbcc');                     -- 反转
select initcap('aabbcc');                     -- 首字母大写
select replace('aabbcc', 'bc', 'xy');         -- 替换
select left('abcde', 2);                      -- 左截取
select right('abcde', 2);                     -- 右截取
---------------------------------------------------------------------------------------------- 01.查询连接数
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
-- 查询表架构和表名
select oid,table_catalog,table_schema,table_name
from information_schema.tables t1, pg_class t2 
where table_schema = 'bd' and t1."table_name" = t2.relname
order by table_catalog,table_schema,table_name;

-- 查询字段信息
select e.table_catalog, e.table_schema, c.relname "tab", cast(obj_description(c.oid) as varchar) "desc", a.attnum "num", a.attname "col",
  d.typname "type", concat_ws('', d.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) from '(.*)')) "type2", b.description "bak"
from pg_attribute a left join pg_description b on b.objoid = a.attrelid and b.objsubid = a.attnum
  left join pg_class c on a.attrelid = c.oid
  left join pg_type d on a.atttypid = d.oid
  left join information_schema.tables e on e.table_name = c.relname
where a.attnum >= 0 and reltype > 0 and a.attname = 'verid' and d.typname != 'int4' --and relnamespace in (29257,20582)
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 
@@ -28,7 +58,7 @@
  select a.* from lf.sys_menu a, rs b where a.pid=b.id
) select * FROM rs order by order_num; -- 查询菜单

with recursive rs as(
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
@@ -39,6 +69,10 @@
union
  select a.* from lf.sys_dir a, rs b where a.pid=b.id
) select * FROM rs order by order_num; -- 数据目录

with recursive rs as(select id, pid from lf.sys_dir where id in (2,5,7,9,12)
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; -- 数据目录
---------------------------------------------------------------------------------------------- 03.查询表结构
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;
@@ -266,11 +300,37 @@
select * from lf.sys_token where to_char(create_time,'yyyy-MM-dd') = '2022-09-30';

select fn_rec_query(depid,'dep') depName,fn_rec_query(dirid,'dir') dirName,fn_ver(verid) verName,fn_uname(createuser) createName,fn_uname(updateuser) updateName from bd.dlg_agnp limit 5;
---------------------------------------------------------------------------------------------- -1.测试
select id,name from lf.sys_dir where name in ('测量（ESV）','勘察（EGE）','地灾（EGD）','洞库（EGD）') order by name;

select (select string_agg(cast(id as varchar),',') from lf.sys_dir where name = a.name) "key",name "value" from lf.sys_dir a
where name in ('测量（ESV）','勘察（EGE）','地灾（EGD）','洞库（EGD）') group by name order by name;
select ns,tab from lf.sys_dict a where not exists (select id from lf.sys_dict b where b.ns=a.ns and b.tab=a.tab and b.field='gid') group by ns,tab; -- 查询字典中不存在gid的表
---------------------------------------------------------------------------------------------- 
-- 10进制转62进制
WITH RECURSIVE T(N, S) AS (
    SELECT 3843::NUMERIC(30, 0) 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 FROM T WHERE N = 0;

-- 62进制转10进制
WITH RECURSIVE T(S, N) AS (
    SELECT 'ZZ' 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::NUMERIC(30, 0) FROM T WHERE LENGTH(S) < 1;
---------------------------------------------------------------------------------------------- -1.测试
select (select string_agg(code, ',') from lf.sys_dir where name = a.name) "key", name "value"
from lf.sys_dir a
where name in ('基础测绘', '基础地灾', '基础勘察', '合规数据', '管理数据', '测量（ESV）', '勘察（EGE）', '地灾（EGD）', '洞库（EGD）')
group by name
order by key;