
create or replace function construct_length_triggers() returns text
  language plpgsql as
$CUERPO$
declare
  v_comando text;
  v_cursor record;
begin
  v_comando = $COMANDO$
create or replace function aglo_princ_length_trg() returns trigger 
  language plpgsql as
$BODY$
begin
  if new.cod_aglo_princ is not null then
    if trim(new.cod_aglo_princ)='' then
      new.cod_aglo_princ := null;
    else 
      if new.cod_aglo_princ <> trim(new.cod_aglo_princ) then
        new.cod_aglo_princ :=  trim(new.cod_aglo_princ);
      end if;
      if length(new.cod_aglo_princ)>4 then
        raise exception 'aglo_princ % excede la longitud maxima 4', new.cod_aglo_princ;
      else
        while length(new.cod_aglo_princ)<4 loop
          new.cod_aglo_princ := '0' || new.cod_aglo_princ;
        end loop;
      end if;
    end if;
  end if;
  return new;
end;
$BODY$;
$COMANDO$;
  for v_cursor in 
    SELECT 'aglo' as campo     , 4 as max_length 
      UNION SELECT 'aglo_princ', 4
      UNION SELECT 'prov'      , 2
      UNION SELECT 'muni'      , 9
      UNION SELECT 'reg'       , 1
      UNION SELECT 'dpto'      , 5
  loop
    execute replace(replace(v_comando,'aglo_princ',v_cursor.campo),'4',v_cursor.max_length::text);
  end loop;
  return 'ok';
end;
$CUERPO$;

select construct_length_triggers();

/*
create or replace function reg_ut_trg() returns trigger 
  language plpgsql as
$BODY$
begin
  if TG_OP='DELETE' then
    delete from unidades_territoriales 
      where cod_ut=new.cod_reg;
    return old;
  elsif TG_OP='INSERT' then
    insert into unidades_territoriales (cod_ut, nom_ut, nivel_ut, COD_REG)
      values (new.cod_reg,new.nom_reg,'R',new.COD_REG);
  elsif TG_OP='UPDATE' then
    update unidades_territoriales 
      set nom_ut=new.nom_reg
      where cod_ut=new.cod_reg;
  end if;
  return new;
end;
$BODY$;
*/
CREATE TRIGGER d1_prov_length_trg BEFORE INSERT ON provincias    FOR EACH ROW EXECUTE PROCEDURE prov_length_trg();
CREATE TRIGGER d1_prov_length_trg BEFORE INSERT ON aglomerados   FOR EACH ROW EXECUTE PROCEDURE prov_length_trg();
CREATE TRIGGER d1_aglo_length_trg BEFORE INSERT ON aglomerados   FOR EACH ROW EXECUTE PROCEDURE aglo_length_trg();
CREATE TRIGGER d1_prov_length_trg BEFORE INSERT ON municipios    FOR EACH ROW EXECUTE PROCEDURE prov_length_trg();
CREATE TRIGGER d1_aglo_length_trg BEFORE INSERT ON municipios    FOR EACH ROW EXECUTE PROCEDURE aglo_princ_length_trg();
CREATE TRIGGER d1_muni_length_trg BEFORE INSERT ON municipios    FOR EACH ROW EXECUTE PROCEDURE muni_length_trg();
CREATE TRIGGER d1_dpto_length_trg BEFORE INSERT ON departamentos FOR EACH ROW EXECUTE PROCEDURE dpto_length_trg();
CREATE TRIGGER d1_dpto_length_trg BEFORE INSERT ON municipios    FOR EACH ROW EXECUTE PROCEDURE dpto_length_trg();

-- CREATE TRIGGER d5_reg_ut_trg BEFORE INSERT OR UPDATE OR DELETE ON regiones    FOR EACH ROW EXECUTE PROCEDURE reg_ut_trg();
