[pgsql] Modelo de Trigger de Auditoria en PostgreSQL

tg_auditoria.sql

-- Nota: Se deben sustituir los RAISE por INSERT en las tablas de auditoria

CREATE OR REPLACE FUNCTION auditoria() RETURNS TRIGGER 
AS $aud$
DECLARE
    inst TEXT;	
    old_v TEXT;
    new_v TEXT;
    metadata_record RECORD;	
BEGIN
   RAISE NOTICE '(%) -> % [%,%,%]',TG_OP, TG_TABLE_NAME,current_user,current_time,current_date;

   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 (TG_OP = 'INSERT') THEN	
	EXECUTE 'SELECT ($1).' || metadata_record.nth_col_name || '::text' INTO new_v USING NEW; 
	RAISE NOTICE '% -> %',metadata_record.nth_col_name,new_v;

      ELSIF (TG_OP = 'UPDATE') THEN	
	EXECUTE 'SELECT ($1).' || metadata_record.nth_col_name || '::text' INTO old_v USING OLD; 
	EXECUTE 'SELECT ($1).' || metadata_record.nth_col_name || '::text' INTO new_v USING NEW; 
	IF old_v<>new_v THEN
	    RAISE NOTICE '%: % -> %',metadata_record.nth_col_name,old_v,new_v;
        END IF;
      ELSIF (TG_OP = 'DELETE') THEN	
	EXECUTE 'SELECT ($1).' || metadata_record.nth_col_name || '::text' INTO old_v USING OLD; 
	
	RAISE NOTICE '% -> %',metadata_record.nth_col_name,old_v;
     		
      END IF;
      
   END LOOP;	

   RETURN NULL;
END $aud$ LANGUAGE plpgsql;

DROP TRIGGER tg_auditar ON movimientos;

CREATE TRIGGER tg_auditar 
AFTER INSERT OR UPDATE OR DELETE
ON movimientos
FOR EACH ROW
EXECUTE PROCEDURE auditoria();