重现vfp的sys(2015)系统函数,即使用当前的系统时间生成一个36进制的9位字符,在某一时刻保证唯一
也可以接受某个日期作为参数转换成指定的字符串
可以利用本函数在同一毫秒间隔期间中不只一次地调用 SYS(2015) 返回一个唯一的字符串。
需要先创那函数milliseconds
CREATE OR REPLACE FUNCTION public.sys2015(
texpression timestamp without time zone DEFAULT NULL::timestamp without time zone)
RETURNS character
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
lnMilliSecs int;
lnMilliSeconds int;
lnDays int;
lcBase36 varchar(100) default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
lResult varchar(9) default '';
ldNowDate timestamp without time zone;
ldFirstDayDate timestamp without time zone;
lnCounter int default 0;
-- 如果没有传送参数,保证本函数产生唯一值
isCheck boolean default false;
llastdate timestamp without time zone;
lastvalue char(9) ;
lxmin int;
lxmax int;
rightc char(1);
tryTimes int default 0;
BEGIN
--* If no milliseconds are given, use the default value of 0
IF tExpression is null then
tExpression := clock_timestamp();
isCheck := true;
End if;
loop
lResult := '';
lnMilliSeconds := milliseconds(tExpression)/1000;
--Set @lnMilliSeconds=0
--*The total millisecs since midnight
lnMilliSecs := (date_part('hour',tExpression)*3600+
date_part('minute',tExpression)*60+
date_part('seconds',tExpression)::int
)*1000+lnMilliSeconds;
--*The total days since Jan 1st '00 (Where the total days in a (Full) year is 367)
--nDays=TTOD(tDateTime)-DATE(YEAR(tDateTime),1,1)+1+MOD(YEAR(tDateTime),100)*367
lnDays := EXTRACT(DOY from tExpression)+date_part('year',tExpression)::int % 100*367;
--*The table used to 'display' the right value in the string.
--*The sys2015 uses a Base36, where 0=0 and Z=35
--lcBase36='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
--Set *Make the resultstring empty
-- Set @lcSys2015=''
--*We are going to construct the sys2015 string from back to front.
--*First the total millisecs
lnCounter := 0;
while lnCounter<6 loop
lnCounter := lnCounter+1;
lResult := substring(lcBase36,lnMilliSecs%36+1,1) || lResult;
lnMilliSecs := lnMilliSecs/36;
End loop;
--*Second the days since Jan 1st '00
lnCounter := 0;
while lnCounter<3 loop
lnCounter := lnCounter+1;
lResult := substring(lcBase36,lnDays%36+1,1)||lResult;
lnDays := lnDays/36;
End loop;
--*Third, the Underscore
--Set @lcSys2015='_'+@lcSys2015
--*Return the string
if (isCheck) then
if not exists(SELECT oid FROM pg_class WHERE relname = 'sys2015_helper') then
create table IF NOT EXISTS sys2015_helper(id int primary key ,lastdate timestamp without time zone, fvalue char(9) ,counter int default 0);
insert into sys2015_helper(id,lastdate,fvalue)values(1,tExpression,lResult);
raise notice 'tExpression : %', tExpression;
return lResult;
end if;
select lastdate ,fvalue ,xmin,xmax
into llastdate ,lastvalue,lxmin,lxmax
from sys2015_helper
where id = 1;
lnCounter := 0;
if tExpression > llastdate then
update sys2015_helper
set
lastdate = tExpression,
fvalue = lResult,
counter = tryTimes
where id = 1
and xmin = lxmin
and xmax = lxmax;
GET DIAGNOSTICS lnCounter = ROW_COUNT;
end if;
if (lnCounter=0) or (tExpression <=llastdate) then
-- 更新失败 或 获取的时间过时了
tExpression := tExpression + '1 milliseconds'::interval;
tryTimes := tryTimes + 1;
continue;
else
exit;
end if;
else
-- 不用check
exit;
end if;
end loop;
RETURN lResult;
/*
-- 如果因为瞬态导致PK冲突了,继续调用
exception when others then
select sys2015() into lResult;
return lResult;
*/
END;
$BODY$;
ALTER FUNCTION public.sys2015(timestamp without time zone)
OWNER TO postgres;
COMMENT ON FUNCTION public.sys2015(timestamp without time zone)
IS '
author : benson
date : 2018.12.04 1114
使用36进制的字符串表示日间(含时分秒及毫秒),本函数会自动创建表: sys2015_helper
参数
tExpression
将要转换的日期,如果没有传递或null的话,将使用当前的日期
返回值
9位字符串
说明
返回的字符串是以指定日期或系统日期和系统时间来创建的。可以利用本函数在同一毫秒间隔期间中不只一次地调用 SYS(2015) 返回一个唯一的字符串。
示例
select sys2015()
';