From 70ddec6cf736c0174bafaef0a9bfe4c9412af133 Mon Sep 17 00:00:00 2001
From: 13693261870 <252740454@qq.com>
Date: 星期六, 25 三月 2023 16:37:32 +0800
Subject: [PATCH] 1
---
data/db_fn.sql | 89 +++++++++++++++++++++-----------------------
1 files changed, 42 insertions(+), 47 deletions(-)
diff --git a/data/db_fn.sql b/data/db_fn.sql
index 628101f..27afa70 100644
--- a/data/db_fn.sql
+++ b/data/db_fn.sql
@@ -320,14 +320,11 @@
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);
- end if;
-
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 $$
@@ -359,49 +356,6 @@
return new;
end;
$$ language plpgsql;
------------------------------------------------------------------------------------------------------ 18.鍚屾闄勪欢鍑芥暟
--- drop function fn_auto_attach(integer);
-create or replace function fn_auto_attach(sid integer) returns void as $$
- declare
- rec record;
- str varchar;
- tab varchar;
- pid varchar;
- field varchar := 'materiname';
- begin
- select pg_ns || '.' || tcdm, parentid into tab, pid from lf.sys_fme_log a where a.id = sid limit 1;
-
- 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);
-select * from lf.sys_attach where tab = 'bs.m_marker';
-
-select position('s_pac_hydrogeology' in 'b_pac_hydrogeology,bd.b_pac_frozensoil,bd.b_pac_geologic_hazard')
-select parentid, pg_ns || '.' || tcdm from lf.sys_fme_log where id = 143;
-select * from lf.sys_meta where eventid = '1caa28e2-b911-40e1-bfc5-4959a927cdd1';
-select * from bs.m_marker where parentid = '1caa28e2-b911-40e1-bfc5-4959a927cdd1';
-select * from lf.sys_fme_log
-select * from lf.sys_meta a inner join lf.sys_fme_log b on a.eventid= b.parentid
-select * from lf.sys_attach where tab='bs.m_marker';
-
-select * from lf.sys_meta where name in ('璀︾ず鐗�10002.jpg','璀︾ず鐗�10004.jpg') order by id desc
----------------------------------------------------------------------------------------------------- 19.璺緞鍒嗘瀽
-- 鍒犻櫎宸插瓨鍦ㄧ殑鍑芥暟
-- drop function pgr_fromAtoB(varchar, double precision, double precision, double precision, double precision);
@@ -693,4 +647,45 @@
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);
-----------------------------------------------------------------------------------------------------
--
Gitblit v1.9.3