From 06492d128df5d7d0905539dd04faa9d475e9de59 Mon Sep 17 00:00:00 2001
From: 13693261870 <252740454@qq.com>
Date: 星期一, 13 二月 2023 15:12:59 +0800
Subject: [PATCH] 1
---
data/db_fn.sql | 147 ++++++++++++++++++++++++++++++++++++++++--------
1 files changed, 121 insertions(+), 26 deletions(-)
diff --git a/data/db_fn.sql b/data/db_fn.sql
index afd9235..1aa9547 100644
--- a/data/db_fn.sql
+++ b/data/db_fn.sql
@@ -158,23 +158,7 @@
$$ language plpgsql;
select fn_get_entity('dlg_25w_boua_s');
------------------------------------------------------------------------------------------------------ 08.FME鏃ュ織瑙﹀彂鍣�
-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;
-
-create or replace trigger fme_log_trigger after insert on lf.sys_fme_log
-for each row execute procedure fn_meta_insert();
-
-select * from lf.sys_meta;
-/*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';*/
------------------------------------------------------------------------------------------------------ 09.鏌ヨ瀛楀吀琛ㄥ苟缁熻璁板綍
+----------------------------------------------------------------------------------------------------- 08.鏌ヨ瀛楀吀琛ㄥ苟缁熻璁板綍
-- drop function fn_tab_count(varchar, varchar, varchar, integer);
create or replace function fn_tab_count(ns varchar, tab varchar, dirs varchar, depid integer)
returns integer as $$
@@ -208,9 +192,9 @@
select fn_rec_array(1, 'dep')
select count(*) from lf.sys_meta where depid = ANY(fn_rec_array(1, 'dep'));
select count(*) from bd.dlg_25w_lrdl where depid = ANY(fn_rec_array(1, 'dep'));
------------------------------------------------------------------------------------------------------ 10.10杩涘埗杞�62杩涘埗
--- drop function fn_10to62(numeric);
-create or replace function fn_10to62(num numeric(30, 0))
+----------------------------------------------------------------------------------------------------- 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;
@@ -230,10 +214,10 @@
end;
$$ language plpgsql;
-select fn_10to62(3843); select fn_10to62(0); select fn_10to62(3844);
------------------------------------------------------------------------------------------------------ 11.62杩涘埗杞�10杩涘埗
--- drop function fn_62to10(numeric);
-create or replace function fn_62to10(ch varchar)
+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);
@@ -249,8 +233,119 @@
end;
$$ language plpgsql;
-select fn_62to10('zz'); select fn_62to10('0'); select fn_62to10('100');
------------------------------------------------------------------------------------------------------ 12.璺緞鍒嗘瀽
+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
+ cc integer;
+ rid integer;
+ code varchar;
+ 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 := fn_set_tab_codes(rid, genre);
+ end loop;
+
+ return 1;
+ end;
+$$ language plpgsql;
+
+select fn_set_tab_codes(1, 1); select fn_set_tab_codes(0, 2);
+----------------------------------------------------------------------------------------------------- 13.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;
+
+-- drop trigger fme_log_trigger on lf.sys_fme_log;
+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;
+/*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';*/
+----------------------------------------------------------------------------------------------------- 14.鍗曚綅琛ㄨЕ鍙戝櫒
+-- 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;
+
+-- 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;*/
+----------------------------------------------------------------------------------------------------- 15.鐩綍琛ㄨЕ鍙戝櫒
+
+
+----------------------------------------------------------------------------------------------------- 16.璺緞鍒嗘瀽
-- 鍒犻櫎宸插瓨鍦ㄧ殑鍑芥暟
drop function pgr_fromAtoB(varchar, double precision, double precision, double precision, double precision);
--
Gitblit v1.9.3