Matías Neiff's Blog

Bueno gente, cuando empecé a investigar este tema ví muchas versiones, algunos que decían que era imposible hacerlo con pgsql, otros que sí o sí había que usar plperl, otros que solo con tablelog, en fin.. los que me conocen saben que cuanto más difícil parece la tarea más ganas me dan de hacerla.El escenario es el siguiente, tenemos varías tablas en una base de datos, y queremos que cuando se hagan cambios en esa tabla se cree otra tabla que guarde estos cambios.

Bueno, sin mucha más alaraca vamos a los bifes:
Creamos el schema para tener todo ordenadito 😉

CREATE SCHEMA actividad;

Creamos una función que cree la tabla cuando sea necesario:

create or replace function creartablaactividad(tabla TEXT) returns void as
$$
DECLARE
newtable text;
tabla text;
txtquery text;
begin
tabla:=$1;
select tabla || '_actividad' into newtable;
	if not exists(select * from information_schema.tables where table_name = newtable and table_schema = 'actividad') then
    	select 'create table actividad.' ||newtable || ' as select text(''1'') as usuario, current_timestamp as tspam, text(''I'') as operacion, * from ' || tabla ||' limit 1;' into txtquery;
		EXECUTE txtquery;
		select 'delete from actividad.' ||newtable || ';' into txtquery;
		EXECUTE txtquery;
	end if;
end;
$$
language 'plpgsql';

Creamos un trigger genérico para todas las tablas, si bien lo anterior no fue fácil, esto fue lo realmente difícil aunque no lo parezca.

CREATE OR REPLACE FUNCTION process_audit()
  RETURNS trigger AS
$BODY$
DECLARE
newtable text;
col information_schema.columns %ROWTYPE;
txtquery text;
line_old TEXT;
tmpquery text;
i int;
columns_old text[];
BEGIN
select TG_RELNAME || '_actividad' into newtable;
PERFORM creartablaactividad(TG_RELNAME);
        IF (TG_OP = 'DELETE') THEN
			line_old := TRIM( substr(OLD::text,2,(select length(OLD::text)-2)) );
			columns_old := STRING_TO_ARRAY( line_old, ',' );
			i := 0;
			tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
			tmpquery := replace(tmpquery,','''',',',NULL,');
			SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''D'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
			EXECUTE txtquery;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
			line_old := TRIM( substr(OLD::text,2,(select length(OLD::text)-2)) );
			columns_old := STRING_TO_ARRAY( line_old, ',' );
			i := 0;
			tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
			tmpquery := replace(tmpquery,','''',',',NULL,');
			tmpquery := replace(tmpquery,','''',',',NULL,');
			SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''ANT'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
			EXECUTE txtquery;
			line_old := TRIM( substr(NEW::text,2,(select length(NEW::text)-2)) );
			columns_old := STRING_TO_ARRAY( line_old, ',' );
			i := 0;
			tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
			tmpquery := replace(tmpquery,','''',',',NULL,');
			SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''U'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
			EXECUTE txtquery;
           RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
			line_old := TRIM( substr(NEW::text,2,(select length(NEW::text)-2)) );
			columns_old := STRING_TO_ARRAY( line_old, ',' );
			i := 0;
			tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
			tmpquery := replace(tmpquery,','''',',',NULL,');
			SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''I'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
			EXECUTE txtquery;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Lo difícil de esta función es que no se puede usar NEW dentro del select, porque recibiríamos un error como: NEW used in query that is not in a rule

Por lo tanto hay que serilizar el registro y sacarle los parentesis para que quede bien bonito, o sea, la mágia está en: substr(NEW::text,2,(select length(NEW::text)-2))

Creamos ahora el trigger sobre las tablas que queremos supervisar.

CREATE TRIGGER prueba_audit
AFTER INSERT OR UPDATE OR DELETE ON prueba
    FOR EACH ROW EXECUTE PROCEDURE process_audit();

Probamos.
geoprop2=# insert into prueba values (19,19,1);
INSERT 7280876 1
geoprop2=# select * from actividad.prueba_actividad ;
usuario | tspam | operacion | idsegmentoscalle1 | idsegmentoscalle2 | idregiones
----------+-------------------------------+-----------+-------------------+-------------------+------------
postgres | 2010-07-23 18:17:34.155953-07 | I | 19 | 19 | 1
(1 row)
geoprop2=# update prueba set idregiones = 2 where idsegmentoscalle1 = 19 and idsegmentoscalle2 =19;
UPDATE 1
geoprop2=# select * from actividad.prueba_actividad ;
usuario | tspam | operacion | idsegmentoscalle1 | idsegmentoscalle2 | idregiones
----------+-------------------------------+-----------+-------------------+-------------------+------------
postgres | 2010-07-23 18:17:34.155953-07 | I | 19 | 19 | 1
postgres | 2010-07-23 18:18:26.512486-07 | ANT | 19 | 19 | 1
postgres | 2010-07-23 18:18:26.512486-07 | U | 19 | 19 | 2
(3 rows)

geoprop2=# delete from prueba where idsegmentoscalle1 = 19 and idsegmentoscalle2 = 19;
DELETE 1
geoprop2=# select * from actividad.prueba_actividad ;
usuario | tspam | operacion | idsegmentoscalle1 | idsegmentoscalle2 | idregiones
----------+-------------------------------+-----------+-------------------+-------------------+------------
postgres | 2010-07-23 18:21:42.095778-07 | I | 19 | 19 | 1
postgres | 2010-07-23 18:21:46.706143-07 | ANT | 19 | 19 | 1
postgres | 2010-07-23 18:21:46.706143-07 | U | 19 | 19 | 2
postgres | 2010-07-23 18:21:57.163508-07 | D | 19 | 19 | 2
(4 rows)

Espero les guste y sirva 😉

Tags: funciones, NEW used in query that is not in a rule, plpgsql, postgres, postgresql, tablelog, trigger

This entry was posted on Friday, July 23rd, 2010 at 10:41 pm and is filed under Programación, Servidores Linux, SIG. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.