PostgreSQLの自作シリアル関数
10文字のシリアルキー生成関数。
2658年7月9日で1周します。
1秒間に238328回以上呼ばれると重複します。
CREATE SEQUENCE seq_getSerial MAXVALUE 14776335 CYCLE; CREATE FUNCTION to_62base(int4) RETURNS char(6) LANGUAGE SQL IMMUTABLE as' select chr(d6+ case when d6<10 then 48 when d6<36 then 87 else 29 end) || chr(d5+ case when d5<10 then 48 when d5<36 then 87 else 29 end) || chr(d4+ case when d4<10 then 48 when d4<36 then 87 else 29 end) || chr(d3+ case when d3<10 then 48 when d3<36 then 87 else 29 end) || chr(d2+ case when d2<10 then 48 when d2<36 then 87 else 29 end) || chr(d1+ case when d1<10 then 48 when d1<36 then 87 else 29 end) from ( select $1/916132832 as d6, ($1/14776336)%62 as d5, ($1/238328)%62 as d4, ($1/3844)%62 as d3, ($1/62)%62 as d2, $1%62 as d1 ) as d'; CREATE FUNCTION getSerial() RETURNS char(10) LANGUAGE SQL as' select substr(to_62base(CURRENT_DATE - date ''2006-01-01''),4) || substr(to_62base(to_char(CURRENT_TIMESTAMP,''SSSS'')::int4),4) || substr(to_62base(nextval(''seq_getSerial'')::int4),3); ';