trig_func_auditoria.sql

create or replace function auditoria.trig_func_auditoria() returns trigger as
$body$
declare
    nNomeTabela             name;
    nNomeTabelaAuditoria    name;
    tIdAuditoria            auditoria.ta_padrao.id_auditoria%type;
    tDataAuditoria          auditoria.ta_padrao.dt_auditoria%type;
    tOpAuditoria            auditoria.ta_padrao.op_auditoria%type;
    tpid                    auditoria.ta_padrao.pid%type;
    tApplicationName        auditoria.ta_padrao.application_name%type;
    tClientHostName         auditoria.ta_padrao.client_hostname%type;
    metadata_record         RECORD;
    rAuditoria              RECORD;
    tValorAntigo            text;
    tValorNovo              text;
	/*bRegistroAlterado       boolean;*/
begin
    nNomeTabela := TG_TABLE_NAME;
    /*bRegistroAlterado = FALSE;*/
    -- overlay substitui o tb no nome da tabela por ta
    nNomeTabelaAuditoria := 'auditoria.' || overlay(nNomeTabela placing 'ta' from 1 for 2);
    tIdAuditoria:= nextval(nNomeTabelaAuditoria || '_seq');
    tDataAuditoria := current_timestamp; --clock_timestamp()
    tOpAuditoria := substring(TG_OP, 1, 1);
    tpid := pg_backend_pid();
    tApplicationName := auditoria.func_application_name(pg_backend_pid());
    tClientHostName := auditoria.func_client_hostname(pg_backend_pid());

    case tOpAuditoria when 'I', 'U' then
        rAuditoria := new;
    when 'D' then 
        rAuditoria := old;
    end case; 

	if (TG_OP = 'INSERT') or (TG_OP = 'DELETE') then
		execute 'insert into ' || nNomeTabelaAuditoria || ' ' ||
                'select $1, $2, $3, $4, $5, $6, $7.*' 
                using tIdAuditoria, tDataAuditoria, tOpAuditoria, tpid, tApplicationName, tClientHostName, rAuditoria;
	elsif (TG_OP = 'UPDATE') then
        for metadata_record in
            SELECT attname::varchar AS "nth_col_name", pg_type.typname::varchar as "nth_col_type", pg_attribute.attnum
            FROM pg_attribute, pg_class, pg_type 
            WHERE attrelid = pg_class.oid 
            AND pg_attribute.attisdropped = False 
            AND relname = TG_TABLE_NAME 
            AND attnum > 0 
            AND atttypid = pg_type.oid
        loop		
            if ((metadata_record.nth_col_name != 'dt_modificacao') and (metadata_record.nth_col_name != 'id_usuario_modificacao')) then
                EXECUTE 'SELECT ($1).' || metadata_record.nth_col_name || '::text' INTO tValorAntigo USING OLD; 
                EXECUTE 'SELECT ($1).' || metadata_record.nth_col_name || '::text' INTO tValorNovo USING NEW; 
                if tValorAntigo <> tValorNovo then
                    /*if not bRegistroAlterado then*/
                        /*bRegistroAlterado = TRUE;*/
                        execute 'insert into ' || nNomeTabelaAuditoria || ' ' ||
                                'select $1, $2, $3, $4, $5, $6, $7.*' 
                                using tIdAuditoria, tDataAuditoria, tOpAuditoria, tpid, tApplicationName, tClientHostName, rAuditoria;                            
                    /*end if;*/
                    -- Caso tenho ocorrido alteração, inseri registro para auditoria e encerra loop
                    return null;
                end if;
            end if;
        end loop;
    end if;
    return null;
end;
$body$
language 'plpgsql';