Bem... Vamos lá ...
Você esta criando uma função para cada tabela que deseja auditar .. ??
Vou postar aqui uma pequena parte do sistema de replicação que estou
desenvolvendo, que é justamente a parte necessária para "LOGAR" as
operações executadas nas tabelas, e salvá-las em duas tabelas (Uma da
operação e outra com os campos e valores alterados/inseridos)....
O detalhe é que isso não pode ser feito em plpgsql, pois esta não
permite a manipulação dinamica dos campos do tipo RECORD, então
preferi usar a plperl, mas também pode ser feita em C ou plphp,
etc....
Aqui vai o protótipo (Funciona, mas pode ser aperfeiçoado em alguns pontos ...)
-- INICIO DO CÓDIGO SQL --
-- Tabela para armazenar as operações executadas
create table tzrepl.tzr_log
(
id_log serial primary key not null,
schema_name varchar,
table_name varchar,
op_type varchar,
time timestamp default current_timestamp,
pk_field varchar,
pk_value varchar,
received char default 'f'
) WITH OIDS;
-- Tabela para armezenar os campos e valores Alterados / Inseridos.
create table tzrepl.tzr_log_detail (
id_log_detail serial primary key not null,
id_log int references tzrepl.tzr_log(id_log) ON DELETE CASCADE ON
UPDATE CASCADE,
column_name varchar,
old_value varchar,
new_value varchar,
time timestamp default current_timestamp
);
-- FUNCAO QUE INSERE OS LOGS NA TABELA
CREATE OR REPLACE FUNCTION tzrepl.tzr_log_command() RETURNS trigger AS $$
#-- Tipo de Evento (INSERT / UPDATE / DELETE)
my $tg_ev = $_TD->{event};
#-- OID da tabela que recebeu o comando
my $table_OID = $_TD->{relid};
#-- Nome da tabela que recebeu o comando
my $table_name_value = $_TD->{relname};
#-- Chave Primária da Tabela
$rs_pk = spi_exec_query("SELECT
tzrepl.tzr_get_pk_field_by_oid($table_OID) AS pk");
if($rs_pk{status} == SPI_OK_SELECT)
{
$pk_column = $rs_pk->{rows}[0]->{pk};
$pk_value = defined $_TD->{"new"}{$pk_column} ?
$_TD->{"new"}{$pk_column} : $_TD->{"old"}{$pk_column};
}
#-- Verifica se não é um registro recebido via replicação
$rs_rcv = spi_exec_query("SELECT tzrepl.tzr_check_received_log() AS
rcv");
if($rs_rcv{status} == SPI_OK_SELECT)
{
$rcv = $rs_rcv->{rows}[0]->{rcv};
}
else
{
$rcv = 'f';
}
#-- Insere o Log
$rs_log = spi_exec_query("INSERT INTO tzrepl.tzr_log
(table_name,op_type,pk_value,pk_field,received) VALUES
('$table_name_value','$tg_ev','$pk_value','$pk_column','$rcv')");
if($rs_log{status} == SPI_OK_INSERT)
{
#-- Obtem o ID do log inserido
$rs_log_value = spi_exec_query("SELECT
currval('tzrepl.tzr_log_id_log_seq') as id_log");
if($rs_log_value{status} == SPI_OK_SELECT)
{
#-- Restaurando o ID do log inserido
$id_log = $rs_log_value->{rows}[0]->{id_log};
#-- Colunas da tabela
$rs_columns = spi_exec_query("SELECT * FROM
tzrepl.tzr_get_table_columns_by_oid($table_OID)");
my $nrows = $rs_columns->{processed};
foreach my $rn (0 .. $nrows - 1)
{
my $row = $rs_columns->{rows}[$rn];
$column = $row->{attname};
$exec = 0;
if($tg_ev eq 'INSERT')
{
$exec = 1;
$old_val = '';
$new_val = $_TD->{new}{$column};
}
elsif($tg_ev eq 'DELETE')
{
$exec = 1;
$old_val = $_TD->{old}{$column};
$new_val = '';
}
elsif($tg_ev eq 'UPDATE')
{
$old_val = $_TD->{old}{$column};
$new_val = $_TD->{new}{$column};
if($_TD->{new}{$column} eq
$_TD->{old}{$column})
{
$exec = 0;
}
else
{
$exec = 1;
}
}
$old_val =~ s/\'/\'\'/g;
$new_val =~ s/\'/\'\'/g;
$old_val = (!defined $old_val) ? "NULL" :
"'$old_val'";
$new_val = (!defined $new_val) ? "NULL" :
"'$new_val'";
if($exec == 1)
{
$query_log_det = "INSERT INTO
tzrepl.tzr_log_detail
(id_log,column_name,new_value,old_value) VALUES
('$id_log','$column',$new_val,$old_val)";
$rs_log_det =
spi_exec_query($query_log_det);
if($rs_log_det{status} != SPI_OK_INSERT)
{
elog(NOTICE,$query_log_det);
}
}
}
}
}
return;
END;
$$ LANGUAGE "plperl";
-- FUNCAO QUE OBTEM A CHAVE PRIMARIA DE UMA TABELA PELO OID DA MESMA
CREATE OR REPLACE FUNCTION tzrepl.tzr_get_pk_field_by_oid(tableoid oid)
RETURNS character varying AS
$BODY$
DECLARE
col VARCHAR;
rsCol RECORD;
BEGIN
SELECT pga.attname as column INTO rsCol FROM pg_class PGC
INNER JOIN pg_constraint PGCO ON PGCO.conrelid = PGC.oid AND PGC.oid =
tableOid
INNER JOIN pg_attribute PGA ON PGA.attnum IN
(array_to_string(PGCO.conkey,','))
AND PGA.attrelid = PGCO.conrelid;
col = rsCol.column;
return col;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-- FIM DO CÓDIGO --
Bem, espero que isso ajude...
Assim que a replicação estiver estável, enviarei para que a lista
possa testar....
Att:
Thiago Risso
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
