P001-根据编码规则自动生成ID的函数

-- 创建sequence
CREATE SEQUENCE seq_1765319714616074242 INCREMENT 1 MINVALUE 3 MAXVALUE 9999 START 3 NO CYCLE NOCACHE ;

-- 变更sequence
ALTER SEQUENCE seq_1765319714616074242 INCREMENT 1 MINVALUE 10 MAXVALUE 9999 START 10 NO CYCLE NOCACHE ;

-- 删除sequence
DROP SEQUENCE dm.seq_commodity;

-- 获取下一次ID
SELECT nextval('seq_1765319714616074242');
-- 获取当前ID,需要执行过nextval后才能执行,否则会报异常
SELECT CURRVAL('seq_1765319714616074242');

-- 删除function
DROP FUNCTION IF EXISTS generate_dm_data_auto_id(bigint);
-- 创建function
CREATE OR REPLACE FUNCTION generate_dm_data_auto_id(num_rule_id bigint)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
DECLARE
    maxVal bigint;
    namePre varchar;
    nameSuf varchar;
    numLen integer;
BEGIN
   select name_pre,name_suf,num_len,nextval(concat('dm_data.seq_',num_rule_id)) into namePre,nameSuf,numLen,maxVal
   FROM dm_id_num_rule where id_num_rule_id = num_rule_id;

  RETURN concat(namePre,LPAD(maxVal::varchar ,numLen,'0'),nameSuf);
END;
$function$
;

-- 使用function
select generate_dm_data_auto_id(1765319714616074242);

日期:2024-03-07