随着项目开发，需要做一个按一定规则递增生成的动态编码，中间按年月后面再加上四位从1开始动态递增的数字，因此写一个存储过程
CREATE OR REPLACE FUNCTION public.generate_code_cwscode(cwsnum character varying)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
DECLARE
curr_max_code VARCHAR;
new_no VARCHAR;
vmodule varchar;
new_date VARCHAR;
new_code VARCHAR;
curr_max_code_no VARCHAR;
begin
new_date := to_char(CURRENT_DATE,'yyyyMM');
vmodule := cwsnum;
EXECUTE 'select max(code) from bs_code where module = ''' || vmodule || ''' ' INTO curr_max_code;
curr_max_code_no := substr(curr_max_code,11,4);
IF curr_max_code is NULL THEN
new_no := '0001';
else
new_no := LPAD(cast(cast(curr_max_code_no as bigint)+1 as varchar),4,'0');
END IF;
new_code := cwsnum || new_date || new_no;
EXECUTE 'insert into public.bs_code (code,module) values (''' || new_code || ''',''' || vmodule || ''')';
return new_code;
END
$function$