----------------------------------------------------------------------------------------------------- 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
|
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(); 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 '||
|
') 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(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;
|
|
if (char_length(str) > 1) then
|
str = substring(str, 2);
|
end if;
|
|
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(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 fn_uname(1);
|
----------------------------------------------------------------------------------------------------- 05.查询版本名
|
-- drop function fn_ver(integer);
|
create or replace function fn_ver(id integer)
|
returns varchar as $$
|
declare
|
str varchar;
|
begin
|
if (id is null) then
|
return null;
|
end if;
|
|
execute 'select name from lf.sys_ver where id = ' || id into str;
|
|
return str;
|
end;
|
$$ language plpgsql;
|
|
select fn_ver(0);
|
----------------------------------------------------------------------------------------------------- 06.递归查询ID数组
|
-- drop function fn_rec_array(integer, 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'));
|
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 %', 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');
|
select fn_tab_count('bd', 'b_rg20w_anna', '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_dir_similar(varchar);
|
create or replace function fn_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
|
str := substr(str, 2);
|
end if;
|
|
return '(' || str || ')%';
|
end;
|
$$ language plpgsql;
|
|
select * from lf.sys_meta where depcode similar to '(00|01|02)%';
|
select fn_dir_similar('基础测绘'); select fn_dir_similar('测绘（ESV）'); select * from lf.sys_meta where dircode similar to fn_dir_similar('基础测绘');
|
----------------------------------------------------------------------------------------------------- 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;
|
|
-- select id, pg_ns || '.' || tcdm, parentid from lf.sys_fme_log;
|
----------------------------------------------------------------------------------------------------- 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;
|
|
----------------------------------------------------------------------------------------------------- 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;
|
|
----------------------------------------------------------------------------------------------------- 17.同步项目名称触发器函数
|
-- drop function fn_bs_prj();
|
create or replace function fn_bs_prj() returns trigger as $$
|
begin
|
if (new.dirid is not null and length(new.dirid) > 1) then
|
select name into new.projname from lf.sys_dir where code = substr(new.dirid, 1, 2) limit 1;
|
end if;
|
|
return new;
|
end;
|
$$ language plpgsql;
|
----------------------------------------------------------------------------------------------------- 19.路径分析
|
-- 删除已存在的函数
|
-- 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);
|
|
/******************************************************************************** 以下为触发器 ********************************************************************************/
|
-- drop trigger fme_log_trigger on lf.sys_fme_log; -- FME日志
|
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';*/
|
|
-- 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;*/
|
|
-- 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;*/
|
|
|
-- drop trigger if exists fn_m_pipeline_trigger on bs.m_pipeline; -- 管线
|
create or replace trigger fn_m_pipeline_trigger before insert on bs.m_pipeline for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_pipesegment_trigger on bs.m_pipesegment; -- 管段
|
create or replace trigger fn_m_pipesegment_trigger before insert on bs.m_pipesegment for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_sitepoint_trigger on bs.m_sitepoint; -- 站场点
|
create or replace trigger fn_m_sitepoint_trigger before insert on bs.m_sitepoint for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_valvehousepoint_trigger on bs.m_valvehousepoint; -- 阀室点
|
create or replace trigger fn_m_valvehousepoint_trigger before insert on bs.m_valvehousepoint for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_crossing_trigger on bs.m_crossing; -- 穿跨越
|
create or replace trigger fn_m_crossing_trigger before insert on bs.m_crossing for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_marker_trigger on bs.m_marker; -- 三桩一牌
|
create or replace trigger fn_m_markert_trigger before insert on bs.m_marker for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_hydraulic_protection_trigger on bs.m_hydraulic_protection; -- 水工保护
|
create or replace trigger fn_m_hydraulic_protection_trigger before insert on bs.m_hydraulic_protection for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_g_collapse_trigger on bs.g_collapse; -- 崩塌
|
create or replace trigger fn_g_collapse_trigger before insert on bs.g_collapse for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_g_debrisflow_trigger on bs.g_debrisflow; -- 泥石流
|
create or replace trigger fn_g_debrisflow_trigger before insert on bs.g_debrisflow for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_g_ground_collapse_trigger on bs.g_ground_collapse; -- 地面塌陷
|
create or replace trigger fn_g_ground_collapse_trigger before insert on bs.g_ground_collapse for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_g_highandsteep_slope_trigger on bs.g_highandsteep_slope; -- 高陡边坡
|
create or replace trigger fn_g_highandsteep_slope_trigger before insert on bs.g_highandsteep_slope for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_g_landslide_trigger on bs.g_landslide; -- 滑坡
|
create or replace trigger fn_g_landslide_trigger before insert on bs.g_landslide for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_g_unstable_slope_trigger on bs.g_unstable_slope; -- 不稳定斜坡
|
create or replace trigger fn_g_unstable_slope_trigger before insert on bs.g_unstable_slope for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_g_water_damage_trigger on bs.g_water_damage; -- 水毁
|
create or replace trigger fn_g_water_damage_trigger before insert on bs.g_water_damage for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_u_underground_hm_trigger on bs.u_underground_hydrological_monitoring; -- 地下水文监测
|
create or replace trigger fn_u_underground_hm_trigger before insert on bs.u_underground_hydrological_monitoring for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_u_sectionline_trigger on bs.u_sectionline; -- 工程地质剖面线
|
create or replace trigger fn_u_sectionline_trigger before insert on bs.u_sectionline for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_detaction_pipelinepoint_trigger on bs.m_detaction_pipelinepoint; -- 地下管线探测成果表
|
create or replace trigger fn_m_detaction_pipelinepoint_trigger before insert on bs.m_detaction_pipelinepoint for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_equipment_nameplate_trigger on bs.m_equipment_nameplate; -- 设备铭牌
|
create or replace trigger fn_m_equipment_nameplate_trigger before insert on bs.m_equipment_nameplate for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_geocontrolpoint_trigger on bs.m_geocontrolpoint; -- 控制点表
|
create or replace trigger fn_m_geocontrolpoint_trigger before insert on bs.m_geocontrolpoint for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_groundline_trigger on bs.m_groundline; -- 地面线表
|
create or replace trigger fn_m_groundline_trigger before insert on bs.m_groundline for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_pipelinepoint_trigger on bs.m_pipelinepoint; -- 中线成果表
|
create or replace trigger fn_m_pipelinepoint_trigger before insert on bs.m_pipelinepoint for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_poi_trigger on bs.m_poi; -- 兴趣点
|
create or replace trigger fn_m_poi_trigger before insert on bs.m_poi for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_scatterpoint_trigger on bs.m_scatterpoint; -- 离散点表
|
create or replace trigger fn_m_scatterpoint_trigger before insert on bs.m_scatterpoint for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_m_surface_deformation_data_trigger on bs.m_surface_deformation_data; -- 地表形变数据表
|
create or replace trigger fn_m_surface_deformation_data_trigger before insert on bs.m_surface_deformation_data for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_s_explorationpoint_trigger on bs.s_explorationpoint; -- 勘探点表
|
create or replace trigger fn_s_explorationpoint_trigger before insert on bs.s_explorationpoint for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_s_surveyworksite_trigger on bs.s_surveyworksite; -- 勘察工点
|
create or replace trigger fn_s_surveyworksitet_trigger before insert on bs.s_surveyworksite for each row execute procedure fn_bs_prj();
|
|
-- drop trigger if exists fn_s_survey_information_trigger on bs.s_survey_information; -- 勘察信息表
|
create or replace trigger fn_s_survey_information_trigger before insert on bs.s_survey_information for each row execute procedure fn_bs_prj();
|
|
|
select gid,projname,eventid,pipename,dirid from bs.m_pipeline order by gid desc;
|
/* insert into bs.m_pipeline (eventid,pipename,dirid) values (new_guid(),'Test','01'); delete from bs.m_pipeline where gid > 52; */
|
|
|
|
|
|
/******************************************************************************** 以下为废弃函数 ********************************************************************************/
|
----------------------------------------------------------------------------------------------------- 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
|
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;
|
----------------------------------------------------------------------------------------------------- c.修改表结构
|
-- 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 cc;
|
end;
|
$$ language plpgsql;
|
|
select fn_alter_tab_fields();
|
|
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;
|
----------------------------------------------------------------------------------------------------- d.10进制与62进制互转
|
-- 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;
|
----------------------------------------------------------------------------------------------------- e.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;
|
|
--if new.count > 0 and position(new.tcdm in 'b_pac_hydrogeology,b_pac_frozensoil,b_pac_geologic_hazard,b_pac_marine_meteorological,b_pac_meteorological,m_equipment_nameplate,m_hydraulic_protection,m_marker,s_explorationpoint,u_sectionline') > 0 then
|
-- select fn_auto_attach(new.id, new.pg_ns || '.' || new.tcdm, new.parentid);
|
--end if;
|
|
return new;
|
end;
|
$$ language plpgsql;
|
----------------------------------------------------------------------------------------------------- f.同步附件函数 *
|
-- drop function fn_auto_attach(integer, varchar, varchar);
|
create or replace function fn_auto_attach(sid integer, tab varchar, pid varchar) returns void as $$
|
declare
|
rec record;
|
str varchar;
|
field varchar := 'materiname';
|
begin
|
if position('bs.' in tab) > 0 then field := 'photono'; end if;
|
|
execute format('select count(*) from %s where parentid = ''%s'' and %s is not null', tab, pid, field) into sid;
|
--raise notice 'tab = %, pid = %, field = %, rows = %', tab, pid, field, sid;
|
|
if sid = 0 then return; end if;
|
|
for rec in execute format('select eventid "eid",%s "ns" from %s where parentid=''%s'' and %s is not null', field, tab, pid, field) loop
|
foreach str in array (select string_to_array(replace(replace(replace(rec.ns, '，', ','), '；', ','), ';', ','), ',')) loop
|
--raise notice 'eventid = %, photono = %', rec.eid, str;
|
|
execute format('insert into lf.sys_attach (name, guid, path, sizes, create_user, tab, tab_guid) ' ||
|
'select name, guid, path, sizes, create_user, ''%s'', ''%s'' from lf.sys_meta ' ||
|
'where name = ''%s'' and create_time > now()::timestamp + ''-1 hour'' order by id desc limit 1', tab, rec.eid, str);
|
end loop;
|
end loop;
|
end;
|
$$ language plpgsql;
|
|
-- select fn_auto_attach(142); select fn_auto_attach(143);
|
-----------------------------------------------------------------------------------------------------
|
