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