postgresql 日期生成流水号

 1 --表结构
 2 DROP TABLE if exists public.sys_tabid;
 3 CREATE TABLE public.sys_tabid
 4 (
 5   id serial NOT NULL ,
 6   type character varying(50),
 7   code character varying(50) NOT NULL,
 8   seed integer,
 9   date date
10 );
11 --测试数据
12 insert into sys_tabid(type,code,seed,date) values
13 ('订单PO','119',1,'2017-06-27'),
14 ('仓库','2',1,'2017-06-27'),
15 ('入库PO','9',1,'2017-06-27');
16 
17 --公共函数
18 CREATE OR REPLACE FUNCTION public.p_sys_getid(
19     p_type character varying DEFAULT '仓库'::character varying,
20     p_seed integer DEFAULT 1)
21   RETURNS integer AS
22 $BODY$
23 DECLARE 
24 --SET NOCOUNT ON;
25 DECLARE 
26     v_d date := now()::date;
27     v_code INT := 0;
28     v_rowcount INTEGER;
29     
30 BEGIN
31 -- select * from p_sys_getid();
32 <<tran>> 
33 loop
34 <<TRY>> 
35 loop
36      --IF  NOT EXISTS(SELECT 1 FROM [Sys_TabID] WITH(ROWLOCK) WHERE [type]=@type AND [date]=@d) 
37 
38     IF  NOT EXISTS(SELECT 1 FROM Sys_TabID WHERE type = p_type AND date=v_d) then
39         INSERT INTO Sys_TabID(type,code,seed,date) values(p_type,'1',p_seed,v_d);
40         GET DIAGNOSTICS v_rowcount = ROW_COUNT;
41     end if;
42 
43     IF v_rowcount > 0 then
44          v_code=1;
45         exit tran; 
46     END IF;
47 
48     select code::int+seed::int from Sys_TabID into v_code WHERE type = p_type AND date = v_d;
49     UPDATE Sys_TabID SET code = v_code WHERE type = p_type AND date = v_d;
50         GET DIAGNOSTICS v_rowcount = ROW_COUNT;
51         IF v_rowcount>0 then
52             exit tran; 
53          end if;
54          
55 exit TRY;
56 exit tran;
57 
58 END LOOP;
59 END LOOP;
60 
61 RETURN v_code;
62 
63 END;
64 $BODY$
65   LANGUAGE plpgsql 
66   
67   
68 --查询函数
69 CREATE OR REPLACE FUNCTION public.select_tlsh(
70     letter character varying DEFAULT 'DGR'::character varying,
71     p_type character varying DEFAULT '订单PO'::character varying)
72   RETURNS character varying AS
73 $BODY$
74 DECLARE 
75     --letter varchar(10)='DGR';
76     id int; 
77     TLSH VARCHAR(20); 
78 BEGIN 
79     select * from P_Sys_GetID(p_type,1) into id;
80         TLSH = letter||to_char(now()::timestamp,'YYYYMMDD')||right((1000000+id)::varchar,6);
81          RETURN TLSH;
82 END;
83 $BODY$
84   LANGUAGE plpgsql