postgresql 日期生成流水号

--表结构
DROP TABLE if exists public.sys_tabid;
CREATE TABLE public.sys_tabid
(
  id serial NOT NULL ,
  type character varying(50),
  code character varying(50) NOT NULL,
  seed integer,
  date date
);
--测试数据
insert into sys_tabid(type,code,seed,date) values
('订单PO','119',1,'2017-06-27'),
('仓库','2',1,'2017-06-27'),
('入库PO','9',1,'2017-06-27');

--公共函数
CREATE OR REPLACE FUNCTION public.p_sys_getid(
    p_type character varying DEFAULT '仓库'::character varying,
    p_seed integer DEFAULT 1)
  RETURNS integer AS
$BODY$
DECLARE 
--SET NOCOUNT ON;
DECLARE 
    v_d date := now()::date;
    v_code INT := 0;
    v_rowcount INTEGER;
    
BEGIN
-- select * from p_sys_getid();
<<tran>> 
loop
<<TRY>> 
loop
     --IF  NOT EXISTS(SELECT 1 FROM [Sys_TabID] WITH(ROWLOCK) WHERE [type]=@type AND [date]=@d) 

    IF  NOT EXISTS(SELECT 1 FROM Sys_TabID WHERE type = p_type AND date=v_d) then
        INSERT INTO Sys_TabID(type,code,seed,date) values(p_type,'1',p_seed,v_d);
        GET DIAGNOSTICS v_rowcount = ROW_COUNT;
    end if;

    IF v_rowcount > 0 then
         v_code=1;
        exit tran; 
    END IF;

    select code::int+seed::int from Sys_TabID into v_code WHERE type = p_type AND date = v_d;
    UPDATE Sys_TabID SET code = v_code WHERE type = p_type AND date = v_d;
        GET DIAGNOSTICS v_rowcount = ROW_COUNT;
        IF v_rowcount>0 then
            exit tran; 
         end if;
         
exit TRY;
exit tran;

END LOOP;
END LOOP;

RETURN v_code;

END;
$BODY$
  LANGUAGE plpgsql 
  
  
--查询函数
CREATE OR REPLACE FUNCTION public.select_tlsh(
    letter character varying DEFAULT 'DGR'::character varying,
    p_type character varying DEFAULT '订单PO'::character varying)
  RETURNS character varying AS
$BODY$
DECLARE 
    --letter varchar(10)='DGR';
    id int; 
    TLSH VARCHAR(20); 
BEGIN 
    select * from P_Sys_GetID(p_type,1) into id;
        TLSH = letter||to_char(now()::timestamp,'YYYYMMDD')||right((1000000+id)::varchar,6);
         RETURN TLSH;
END;
$BODY$
  LANGUAGE plpgsql