CREATE FUNCTION fn_generar_cod_microbiologico() RETURNS character varying
    LANGUAGE plpgsql
    AS $$
declare
codigo varchar (13);
maximo varchar(5);
anio2 varchar (4);
anio integer;
begin

anio2 = extract(year from current_date);
anio = to_number (anio2, '9999');

select LPAD((max(h.correlativo)+1)::text,5,'0') as max_corr into maximo from
(select to_number(substring(codigo_analisis_microbiologico,4,5),'99999') correlativo from blh_analisis_microbiologico
where
to_number(substring(codigo_analisis_microbiologico,10,4),'9999') = anio) as h;

if maximo is null
then
    maximo = '00001';
end if;

codigo =  'AM-'|| maximo || '-' || anio;
return codigo;
end;

CREATE FUNCTION fn_generar_cod_receptor(id_blh integer, anio integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
declare
codigo varchar (14);
 maximo varchar(5);
 siguiente integer;
 siguiente2 integer;
 id_blh2 varchar(3);
begin
id_blh2 = LPAD(id_blh::text,2,'0')||'%';
select LPAD((max(h.correlativo)+1)::text,5,'0') as max_corr into maximo from  (select to_number(substring(codigo_receptor,5,6),'99999') correlativo from blh_receptor
where
codigo_receptor like id_blh2
and to_number(substring(codigo_receptor,11,4),'99999') = anio) as h;

if maximo is null
then
    maximo = '00001';
end if;

codigo = (select LPAD(id_blh::text,2,'0'))|| '-' || 'R' || maximo || '-' || anio;
return codigo;
end;