postgreSql版的sys2015函数

重现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()
	
';