From 9bce48a9f30f7d80c43f43f46d40df20fcb00e15 Mon Sep 17 00:00:00 2001
From: 13693261870 <252740454@qq.com>
Date: 星期五, 03 二月 2023 09:39:59 +0800
Subject: [PATCH] 1
---
data/db_fn.sql | 40 +++++++++++++++++++++++++++++++++++++++-
1 files changed, 39 insertions(+), 1 deletions(-)
diff --git a/data/db_fn.sql b/data/db_fn.sql
index 2056b29..1fbb40f 100644
--- a/data/db_fn.sql
+++ b/data/db_fn.sql
@@ -208,7 +208,45 @@
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.10杩涘埗杞�62杩涘埗
+-- drop function fn_10to62(numeric);
+create or replace function fn_10to62(num numeric(30, 0))
+returns varchar as $$
+ declare
+ rs varchar;
+ begin
+ 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_10to62(3843);
+----------------------------------------------------------------------------------------------------- 11.62杩涘埗杞�10杩涘埗
+-- drop function fn_62to10(numeric);
+create or replace function fn_62to10(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_62to10('zz');
+----------------------------------------------------------------------------------------------------- 12.璺緞鍒嗘瀽
-- 鍒犻櫎宸插瓨鍦ㄧ殑鍑芥暟
drop function pgr_fromAtoB(varchar, double precision, double precision, double precision, double precision);
--
Gitblit v1.9.3