Pessoal, estou usando a função abaixo para chamar de triggers no after
insert, update e delete das tabelas que desejo fazer logging. Ta funcionando
ok para todas as tabelas, exceto para uma, para a qual recebe a mensagem de
que não foi possível encontrar o campo "tgname". Segue tambem abaixo a
definição da tabela para a qual o trigger não funciona. Se alguém tiver uma
luz, da um toque ai.
CREATE OR REPLACE FUNCTION "public"."log_to_audit_table" () RETURNS trigger
AS
$body$
spi_exec "SELECT CURRENT_USER AS tguser"
spi_exec "SELECT relname AS tgname FROM pg_class WHERE relfilenode =
$TG_relid"
#skip changes on audit_table
if {[string equal -nocase $tgname audit_table]} { return OK } //creio que
o erro seja aqui.
#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 = '$tgname'
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', '$tgname', '$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', '$tgname', '$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', '$tgname', '$modified_field',
'$pk_name', '$pk_value', '$TG_op', '$previous_value', NULL)"
}
}
}
}
return OK
$body$
LANGUAGE 'pltcl' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE TABLE "public"."lanctos_contab" (
"id" BIGINT NOT NULL,
"id_conta_cred" INTEGER,
"id_conta_deb" INTEGER,
"id_empresa" INTEGER NOT NULL,
"id_terceiro" INTEGER,
"flag_origem" SMALLINT,
"chave_origem" BIGINT,
"origem" CHAR(2),
"valor" NUMERIC(18,2),
"data_lancto" DATE NOT NULL,
"historico" VARCHAR(200) NOT NULL,
"id_centro_custo" INTEGER,
"codigo_lancto" BIGINT,
"documento" VARCHAR(25),
CONSTRAINT "lanctos_contab_pkey" PRIMARY KEY("id"),
CONSTRAINT "lanctos_contab_fk1" FOREIGN KEY ("id_conta_cred")
REFERENCES "public"."plano_contas"("id")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE,
CONSTRAINT "lanctos_contab_fk2" FOREIGN KEY ("id_conta_deb")
REFERENCES "public"."plano_contas"("id")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE,
CONSTRAINT "lanctos_contab_fk3" FOREIGN KEY ("id_empresa")
REFERENCES "public"."empresas"("id")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE,
CONSTRAINT "lanctos_contab_fk4" FOREIGN KEY ("id_terceiro")
REFERENCES "public"."terceiros"("id")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE,
CONSTRAINT "lanctos_contab_fk5" FOREIGN KEY ("id_centro_custo")
REFERENCES "public"."centros_custo"("id")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE
) WITHOUT OIDS;
--
View this message in context:
http://www.nabble.com/Para-quem-entende-de-C-tp25439471p25439471.html
Sent from the PostgreSQL - Brasil mailing list archive at Nabble.com.
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
