Pessoal,
a função abaixo é uma função de log. Gostaria de alterá-la, de forma de ela
ignorasse alterações em campos bytea. Mas não conheço de TCL. Se alguém estiver
familiarizado e puder me ajudar, agradeço.
Jean Domingues.
CREATE OR REPLACE FUNCTION "public"."log_to_audit_table" (
)
RETURNS trigger AS
$body$
spi_exec "SELECT CURRENT_USER AS tguser"
#skip changes on audit_table
if {[string equal -nocase $TG_table_name audit_table]} { return OK }
#get PK name
set pk_name ""
spi_exec "SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a, pg_index
i
WHERE c.relname = '$TG_table_name'
AND c.oid=i.indrelid
AND a.attnum > 0
AND a.attrelid = i.indexrelid
AND i.indisprimary='t'"
switch $TG_op {
INSERT {
set pk_value ""
#get PK value
foreach field $TG_relatts {
if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
set pk_value [lindex [array get NEW $field] 1]
break;
}
}
#log inserted row values
foreach field $TG_relatts {
if {! [string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
set modified_field [lindex [array get NEW $field] 0]
set current_value [lindex [array get NEW $field] 1]
spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld, pk_name,
pk_value, mod_type, old_val, new_val)
VALUES (CURRENT_TIMESTAMP, '$tguser', '$TG_table_name',
'$modified_field', '$pk_name', '$pk_value', '$TG_op', NULL, '$current_value')"
}
}
}
UPDATE {
set pk_value ""
#get PK value
foreach field $TG_relatts {
if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
set pk_value [lindex [array get NEW $field] 1]
break;
}
}
#log inserted row values
foreach field $TG_relatts {
#check changed fields
if {[string equal -nocase [array get NEW $field] [array get OLD $field]] ==
0} {
set modified_field [lindex [array get OLD $field] 0]
if {[string compare $modified_field ""] == 0} {
set modified_field [lindex [array get NEW $field] 0]
}
set previous_value [lindex [array get OLD $field] 1]
set current_value [lindex [array get NEW $field] 1]
spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld, pk_name,
pk_value, mod_type, old_val, new_val)
VALUES (CURRENT_TIMESTAMP, '$tguser', '$TG_table_name',
'$modified_field', '$pk_name', '$pk_value', '$TG_op', '$previous_value',
'$current_value')"
}
}
}
DELETE {
set pk_value ""
#get PK value
foreach field $TG_relatts {
if {[string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} {
set pk_value [lindex [array get OLD $field] 1]
break;
}
}
#log inserted row values
foreach field $TG_relatts {
if {! [string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} {
set modified_field [lindex [array get OLD $field] 0]
set previous_value [lindex [array get OLD $field] 1]
spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld, pk_name,
pk_value, mod_type, old_val, new_val)
VALUES (CURRENT_TIMESTAMP, '$tguser', '$TG_table_name',
'$modified_field', '$pk_name', '$pk_value', '$TG_op', '$previous_value', NULL)"
}
}
}
}
return OK
$body$
LANGUAGE 'pltcl'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
