Re: Auditoría genérico de tablas usando hstore

hechale un vistazo a pg_audit, hace cosas por ti, y utiliza hstore.
saludos

----- Mensaje original -----
De: "Jairo Graterón" <jgrateron(at)gmail(dot)com>
Para: "Lista PostgreSQL" <pgsql-es-ayuda(at)postgresql(dot)org>
Enviados: Viernes, 13 de Septiembre 2013 1:45:44
Asunto: [pgsql-es-ayuda] Auditoría genérico de tablas usando hstore

Saludos,

Me gustaria saber que opinion tienen sobre usar hstore para llevar una auditoria de las tablas de la base de datos de la compañia de trabajo.

--CREATE EXTENSION hstore;

DROP TABLE IF EXISTS auditoria;

create table auditoria
(
fechahora timestamp not null,
usuario text not null,
operacion text not null,
nombre_esquema text not null,
nombre_tabla text not null,
antes hstore,
despues hstore
);

CREATE OR REPLACE FUNCTION f_auditoria()
RETURNS trigger AS
$$
begin
IF TG_OP = 'INSERT' THEN
INSERT INTO auditoria(fechahora, usuario, operacion, nombre_esquema, nombre_tabla, antes, despues)
SELECT CURRENT_TIMESTAMP, user, TG_OP, TG_TABLE_SCHEMA,TG_TABLE_NAME,NULL, hstore(new);
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO auditoria(fechahora, usuario, operacion, nombre_esquema, nombre_tabla, antes, despues)
SELECT CURRENT_TIMESTAMP, user, TG_OP, TG_TABLE_SCHEMA,TG_TABLE_NAME,hstore(old), hstore(new);
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO auditoria(fechahora, usuario, operacion, nombre_esquema, nombre_tabla, antes, despues)
SELECT CURRENT_TIMESTAMP, user, TG_OP, TG_TABLE_SCHEMA,TG_TABLE_NAME,hstore(old), NULL;
RETURN OLD;
END IF;
end;
$$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE OR REPLACE FUNCTION f_colocar_auditoria(text)
RETURNS void AS
$$
DECLARE
tabla ALIAS FOR $1;
nombretrigger text;
sql text;
BEGIN
nombretrigger = 'trigger_auditoria_' || replace (tabla,'.','_');
sql = 'CREATE TRIGGER ' || nombretrigger || ' AFTER INSERT OR UPDATE OR DELETE ON '|| tabla || ' FOR EACH ROW EXECUTE PROCEDURE f_auditoria()';
EXECUTE sql;
END;
$$
LANGUAGE plpgsql VOLATILE
COST 100;

------------------------------------------------- ejemplo -------------------------------------------

DROP TABLE IF EXISTS producto;

CREATE TABLE producto
(
idproducto serial NOT NULL,
nombre text NOT NULL,
costo numeric (10,2) NOT NULL,
stock integer NOT NULL,
PRIMARY KEY (idproducto)
);

select f_colocar_auditoria('producto');

DROP TABLE IF EXISTS cliente;

CREATE TABLE cliente
(
idcliente serial not null,
nombre text not null,
direccion text not null,
primary key(idcliente)
);

select f_colocar_auditoria('cliente');

insert into producto (nombre, costo, stock) values ('Monitor', 100.23, 10);
insert into producto (nombre, costo, stock) values ('Teclado', 30, 5);
insert into producto (nombre, costo, stock) values ('Mouse', 25, 50);

update producto set costo=99 where idproducto=1;
update producto set stock=30 where idproducto=3;
update producto set costo=90, stock=8 where idproducto=1;
delete from producto where idproducto=2;

insert into cliente (nombre, direccion) values ('Pepe', 'Bogota');
insert into cliente (nombre, direccion) values ('María', 'Caracas');
insert into cliente (nombre, direccion) values ('Jose', 'Buenos Aires');

update cliente set Nombre='José' where idcliente=3;
update cliente set direccion='Quito' where idcliente=1;
delete from cliente where idcliente=3;

-- pregunta nro 1, listar la auditoria del producto codigo 1
select * from auditoria where nombre_tabla = 'producto'
and (antes -> 'idproducto' = '1' or despues -> 'idproducto' = '1') order by fechahora desc;

-- pregunta nro 2, listar la auditoria del producto codigo 2
select * from auditoria where nombre_tabla = 'producto'
and (antes -> 'idproducto' = '2' or despues -> 'idproducto' = '2') order by fechahora desc;

-- pregunta nro 3 listar la auditoria del cliente codigo 3
select * from auditoria where nombre_tabla = 'cliente'
and (antes -> 'idcliente' = '3' or despues -> 'idcliente' = '3') order by fechahora desc;

--pregunta nro 4. cuales fueron los campos actualizados en el producto nro 1 y cuales eran sus valores antiguos y nuevos
select fechahora, akeys(antes-despues) as campos, avals(antes-despues) as antiguo, avals(despues-antes) as nuevo from auditoria
where operacion='UPDATE' AND nombre_tabla = 'producto'
and (antes -> 'idproducto' = '1' or despues -> 'idproducto' = '1') order by fechahora desc;

__________________________________________________
"Todos el 12 de Septiembre con una Cinta Amarilla"
FIN A LA INJUSTICIA, LIBERENLOS YA!!
http://www.antiterroristas.cu
http://justiciaparaloscinco.wordpress.com

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda(at)postgresql(dot)org)
Para cambiar tu suscripcin:
http://www.postgresql.org/mailpref/pgsql-es-ayuda