PostgreSQL ID生成器

2017-10-28日修改,经过测试,在大并发时,因无锁的原因这个函数会导致id生成有误,请不要再使用,仅供参考.
在执行setval(f.seqdoy,nextval(f.seqdoy) - 1) as sysdoy至setval(f.seqtab, 0)过程中间,如果另一个进程或线程也在执行这段代码,生成的id就错误的id.

以下内容仅供参考

drop function if exists gen_yydoy_textid(text,float8,text);drop function if exists gen_yydoy_id(text,float8);drop sequence  if exists gseq_yydoy;--/*--* 表使用的序列模板示例--* 备注:序列命名方式为 'seq_'+表名--*/--create sequence seq_test--  increment 1--  minvalue 0                  /*注意序列要允许从0开始*/--  maxvalue 99999999       /*最大值要和gen_yydoy_id的第二个参数匹配*/--  start 0                         /*必须从0开始*/--  cache 1000;                 /*预分配数量,根据系统繁忙程度和服务器内存配置调整*/--/*初始化为0*/--select setval('seq_enterprises'::regclass,0);----/*--* 使用方法示例--*/--create table test(--/*--  分布式系统可以使用gen_yydoy_textid没有测试--  创建表成功后,修改每个datanode上的默认值--  比如datanode1和datanode2--  objectid bigint default gen_yydoy_textid('seq_test', 8,'A') not null,--  objectid bigint default gen_yydoy_textid('seq_test', 8,'B') not null,--*/--  objectid bigint default gen_yydoy_id('seq_test', 8) not null,--  name text,--  others jsonb,--  constraint pk_test_objectid primary key(objectid)--);--或者--alter table test--   alter column objectid set default gen_yydoy_id('seq_test', 8);/**   主要为存储每年中第几天天数(DOY),*       备注:作用于整个数据库,每天发生一次变化*/create sequence gseq_yydoy    increment 1    minvalue 1                  /*注意序列要允许从1开始*/    maxvalue 366                /*注意一年之中最多只有366天*/    start 1                         /*从1开始*/    cache 1;                        /*每天变化一次,没必要设置太多*//*初始化为当天*/select setval('gseq_yydoy'::regclass, (date_part('doy' ,now())::bigint));/**   生成YYDOYID格式的编号*       $1:当前表使用的序列名称*       $2:YYDOY与ID之间补0的数量(值范围为1-14)*           比如参数8,日期为2015-10-25,生成结果为1729800000001*           每天最多允许生成99999999条记录*       返回值:bigint类型的编号*       备注:*           1.id每日从1开始编,最大程度上避免浪费*           2.必须要计算好每日的业务量,如果不清楚,可以将第二个参数设置大一点,比如8-10*           3.日期和id值可单独从ID中提取生成*           4.bigint最大值为9223372036854775808,因此第二个参数最大为14*           5.第二个参数值范围为1-14,因过程是sql语言的,只能由dba自己控制*             范围不在1-14之间的话,会导致编码不能正确返回*           6.因编码原因表中数据量永远不可能达到9223372036854775808**/create or replace function gen_yydoy_id(text,float8)     returns bigint as $$with basic as(select      'gseq_yydoy'::regclass as seqdoy,     $1::regclass as seqtab,      now() as cur_time,     pow(10,$2)::bigint as precision),yyyy_doy as(select     date_part('year',f.cur_time)::integer as yyyy,     date_part('doy',f.cur_time)::integer as doy,    1000 as thousand,    setval(f.seqdoy,nextval(f.seqdoy) - 1) as sysdoyfrom basic as f--ouput 2017,298,1000,1),yy as(select     ((s.yyyy - (((s.yyyy/s.thousand)::integer) * s.thousand )) ) as yy,   s.sysdoy = s.doy as doy_equalfrom yyyy_doy as s), check_id as (select     (case when false=t.doy_equal then setval(f.seqdoy, s.doy) end) as new_sysdoy,(case when false=t.doy_equal then setval(f.seqtab, 0)end) as zero_tabid,    nextval(f.seqtab) as new_tabidfrom basic as f,yyyy_doy as s,yy as t), make_id as(select (((t.yy * s.thousand + s.doy) * f.precision) + id.new_tabid) as full_idfrom basic as f,yyyy_doy as s,yy as t,check_id as id) select full_id from make_id;/*select * from basicfull outer join yyyy_doy on 1=1full outer join yy  on 1=1full outer join make_id  on 1=1*/$$ language sql;/**   生成带标志位的YYDOYID格式的编号,标志位在最后*       $1:当前表使用的序列名称*       $2:YYDOY与ID之间补0的数量*           比如参数8,日期为2015-10-25,生成结果为1729800000001*           每天最多允许生成99999999条记录*       备注:此函数适合分布式系统,没有测试*           创建函数后,在每台datanode上修改默认值的第3个参数,用于区分数据存储节点*           第3个参数放在最前面影响排序,,最后不影响排序,因此建议放至最后*           ||操作符的性能比较好.select 'a'||'b'||'c'不建议,复制了三次,性能较差*/create or replace function gen_yydoy_textid(text,float8,text)     returns text as $$select gen_yydoy_id($1,$2) || $3;$$ language sql;--示例--select gen_yydoy_id('seq_enterprises',8),gen_yydoy_id('seq_enterprises',8),gen_yydoy_textid('seq_enterprises',8,'A');