[pgsql] Modelo de Trigger de Auditoria en PostgreSQL

tg_auditoria.sql

DECLARE
    inst TEXT;	
    old_v TEXT;
    new_v TEXT;
	alterado_v BOOLEAN;
    metadata_record RECORD;	
BEGIN
   RAISE NOTICE '(%) -> % [%,%,%]',TG_OP, TG_TABLE_NAME,current_user,current_time,current_date;
alterado_v = FALSE;
raise notice '1°: %', alterado_v;
   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 = '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; 
		raise notice '2°: %', alterado_v;
   		IF old_v<>new_v THEN
		raise notice '3°: %', alterado_v;
   			IF NOT alterado_v THEN
   				alterado_v = TRUE;
				INSERT INTO tb_usuario_logado (id_usuario, id_session, ds_token) VALUES (1,'503m15k3t242gevodhrd5n3fdv','gfb8519f6940f7917932dbe3bef704de661cd1424a2893bb7169255f677e64ea0d93f8e1454b2ee9508311581876391cafe21b6dc5253e6c7414a64858a7496');
   			END IF;   			
   		END IF;
	END IF;
   /*
      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 

-- 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();