Обсуждение: Problem with PgTcl auditing function on trigger
Hi people, I've tried posting on the general list about this, but I never get a reply, so I'm trying here. I have a function that is run each time an INSERT, DELETE or UPDATE happens on a row and log into an audit table. It is based on the info here: http://www.alberton.info/postgresql_table_audit.html We have a table Customers.CREDIT with a primary key "NUMBER", "TRANSNO", "RECNUM". I have a trigger as follows: CREATE TRIGGER "tg_audit_credit" AFTER INSERT OR DELETE OR UPDATE ON "Customers"."CREDIT" FOR EACH ROW EXECUTE PROCEDURE "log_to_audit_table" (); This uses the attached tcl function which basically runs this for a delete 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)" The function works fine for this SQL statement delete from "CREDIT" where "TRANSNO" < 11148188 AND "TRANSNO" > 11148180; However if I try this one I get a syntax error. delete from "CREDIT" where "RECNUM" < 2484907 AND "RECNUM" > 2484905; The error is below. Do I need to escape my strings? And if so how do I do this? Thanks Glyn SEE=# delete from "CREDIT" where "RECNUM" < 2484907 AND "RECNUM" > 2484905; ERROR: syntax error at or near "S" CONTEXT: syntax error at or near "S" while executing "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', '$t..." ("foreach" body line 5) invoked from within "foreach field $TG_relatts { if {! [string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} { set modified_field [lindex [array get..." ("DELETE" arm line 11) invoked from within "switch $TG_op { INSERT { #get PK value foreach field $TG_relatts { if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {..." (procedure "__PLTcl_proc_5667381_trigger_16644" line 23) invoked from within "__PLTcl_proc_5667381_trigger_16644 tg_audit_credit 16644 CREDIT Customers {{} RECNUM TRANSNO NUMBER EXMON EXYEAR OLDTICK COACHES VALUE POSTAGE DEPOSIT..." ___________________________________________________________ Support the World Aids Awareness campaign this month with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/
Вложения
Glyn Astill wrote:
> Hi people,
>
> I've tried posting on the general list about this, but I never get
> a
> reply, so I'm trying here.
I think you'll probably have more luck with a TCL list than the PG
hackers list. However, I've attached some pltcl functions I put together
ages ago to do this sort of thing. Hopefully that will help you.
--
Richard Huxton
Archonet Ltd
-- History Tracking Trigger-Functions
--
BEGIN;
-- tcl_track_history(TABLE-NAME)
-- Set TABLE-NAME when creating the trigger. Will automatically record change
-- details in tables history/history_detail
--
CREATE OR REPLACE FUNCTION tcl_track_history() RETURNS trigger AS '
switch $TG_op {
DELETE {
if { [llength [array names OLD cid]] > 0 } {
set clival $OLD(cid)
} else {
set clival "NULL"
}
spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'D\')"
}
INSERT {
if { [llength [array names NEW cid]] > 0 } {
set clival $NEW(cid)
} else {
set clival "NULL"
}
spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'I\')"
}
UPDATE {
if { [llength [array names OLD cid]] > 0 } {
set clival $OLD(cid)
} else {
set clival "NULL"
}
set inserted_main_history_row false
foreach {col} $TG_relatts {
# First result seems to be an empty string when stepping through columns
if { $col > "" } {
# Check if OLD/NEW contain a value
if { [llength [array names OLD $col]] > 0 } {
set oldval $OLD($col)
} else {
set oldval "NULL"
}
if { [llength [array names NEW $col]] > 0 } {
set newval $NEW($col)
} else {
set newval "NULL"
}
if { $oldval != $newval } {
if { !$inserted_main_history_row } {
spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'U\')"
set inserted_main_history_row true
}
spi_exec "INSERT INTO history_detail (col,was) VALUES (\'$col\', \'[ quote $oldval ]\')"
}
}
}
}
}
return OK
' LANGUAGE pltcl;
CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client FOR EACH ROW EXECUTE PROCEDURE
tcl_track_history('client');
CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client_keyworkers FOR EACH ROW EXECUTE PROCEDURE
tcl_track_history('client_keyworkers');
CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client_notes FOR EACH ROW EXECUTE PROCEDURE
tcl_track_history('client_notes');
COMMIT;
BEGIN;
CREATE OR REPLACE FUNCTION tcl_track_answers() RETURNS trigger AS '
switch $TG_op {
DELETE {
if { [llength [array names OLD cid]] > 0 } {
set clival $OLD(cid)
} else {
set clival "NULL"
}
spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'D\')"
}
INSERT {
if { [llength [array names NEW cid]] > 0 } {
set clival $NEW(cid)
} else {
set clival "NULL"
}
spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'I\')"
}
UPDATE {
# Get question title into var $qn_title
spi_exec "SELECT \'Q\' || qid || \' - \' || title AS qn_title FROM question WHERE qid = $OLD(qid)"
if { [llength [array names OLD cid]] > 0 } {
set clival $OLD(cid)
} else {
set clival "NULL"
}
# Check if OLD/NEW contain a value
if { [llength [array names OLD text_val]] > 0 } {
set oldval $OLD(text_val)
} else {
set oldval "NULL"
}
if { [llength [array names NEW text_val]] > 0 } {
set newval $NEW(text_val)
} else {
set newval "NULL"
}
if { $oldval != $newval } {
spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'U\')"
spi_exec "INSERT INTO history_detail (col,was) VALUES (\'$qn_title\', \'[ quote $oldval ]\')"
}
}
}
return OK
' LANGUAGE pltcl;
CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client_answer FOR EACH ROW EXECUTE PROCEDURE
tcl_track_answers('client_answer');
UPDATE client_answer SET text_val = 'partially sighted',ts=now() WHERE aid=20;
COMMIT;
> > > ----- Original Message ---- > From: Glyn Astill <glynastill@yahoo.co.uk> > To: pgsql-hackers@postgresql.org > Sent: Friday, January 4, 2008 5:23:18 AM > Subject: [HACKERS] Problem with PgTcl auditing function on trigger > > Hi people, > > I've tried posting on the general list about this, but I never get > a > reply, so I'm trying here. > > I have a function that is run each time an INSERT, DELETE or UPDATE > happens on a row and log into an audit table. > > It is based on the info here: > > http://www.alberton.info/postgresql_table_audit.html > > We have a table Customers.CREDIT with a primary key "NUMBER", > "TRANSNO", "RECNUM". > > I have a trigger as follows: > > CREATE TRIGGER "tg_audit_credit" > AFTER INSERT OR DELETE OR UPDATE ON "Customers"."CREDIT" > FOR EACH ROW > EXECUTE PROCEDURE "log_to_audit_table" (); > > This uses the attached tcl function which basically runs this for a > delete > > 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)" > > The function works fine for this SQL statement > > delete from "CREDIT" where "TRANSNO" < 11148188 AND "TRANSNO" > > 11148180; > > However if I try this one I get a syntax error. > > delete from "CREDIT" where "RECNUM" < 2484907 AND "RECNUM" > > 2484905; > Is the Tcl function the same for RECNUM and TRANSNO? I noticed you have set pk_name "RECNUM" ... Not sure if you are changing this when you run the different DELETEs. > > The error is below. Do I need to escape my strings? And if so how > do > I do this? > > Thanks > Glyn > > > SEE=# delete from "CREDIT" where "RECNUM" < 2484907 AND "RECNUM" > > 2484905; > ERROR: syntax error at or near "S" > CONTEXT: syntax error at or near "S" > while executing > "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', '$t..." > ("foreach" body line 5) > invoked from within > "foreach field $TG_relatts { > if {! [string equal -nocase [lindex [array get OLD $field] 0] > $pk_name]} { > set modified_field [lindex [array get..." > ("DELETE" arm line 11) > invoked from within > "switch $TG_op { > INSERT { > > #get PK value > foreach field $TG_relatts { > if {[string equal -nocase [lindex [array get NEW $field] 0] > $pk_name]} {..." > (procedure "__PLTcl_proc_5667381_trigger_16644" line 23) > invoked from within > "__PLTcl_proc_5667381_trigger_16644 tg_audit_credit 16644 CREDIT > Customers {{} RECNUM TRANSNO NUMBER EXMON EXYEAR OLDTICK COACHES > VALUE POSTAGE DEPOSIT..." > I'm not sure where the error is coming from, off hand. The only thing I can think of now is that you may need to [quote] the values or use spi_execp instead. Perhaps there is a ' in there somewhere causing problems. As a side note, just some tips (i realize that you got this from the link above): You have several instances of constructs such as: if {! [string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} { but this really is just $field...so you don't need all of that. You can just do: if {! [string equal -nocase $field $pk_name]} { Similiarily, you have this construct set pk_value [lindex [array get NEW $field] 1] But you can use this instead: set pk_value $NEW($field) And then, this whole block: 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; } } Not sure the purpose here, but you should be able to just do: if {[info exists NEW($pk_name)]} { set pk_value $NEW($pk_name) } else { # something went wrong here...needthis if there's a chance $pk_name might not be there } Note also that TG_relatts has an empty element as the first element of the list, so this if {! [string equal -nocase [lindex[array get OLD $field] 0] $pk_name]} { may be giving you trouble, since it won't catch the empty element. so, you could write that particular loop construct as such: foreach field [lrange $TG_relatts 1 end] { if {! [string equal-nocase $field $pk_name]} { set modified_field $field set previous_value $OLD($field) 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)" } } You may to throw some [elog]'s in there, to see what's going on as well. HTH, --brett ____________________________________________________________________________________ Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping