Обсуждение: Table auditing / Pg/tcl help
Hi people, I've been trying to set up table auditing using a tcl function and a trigger. I followed the guide here to start with: http://www.alberton.info/postgresql_table_audit.html The thing is we have multiple fields in our primary keys so I need to change it to handle them all. I was thinking for now to just set pk_name like "fieldName1,field2Name" and pk_value to "fieldvalue1,fieldvalue2" etc. The script runs the query: "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'" which should produce a list of the values. It then does: #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; } } Which I presume just gets the first value, then breaks. I want to get them all and put them into a comma separated string. So (baering in mind I've not touched tcl before) I changed this to: #get PK value foreach field $TG_relatts { if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} { if {[string length $pk_value] > 0} { append pk_value "," [lindex [array get NEW $field] 1] append pk_list "," $pk_name } else { set pk_value [lindex [array get NEW $field] 1] set pk_list $pk_name } #break; } } But it didn't work, so I guess I'm missing something. ___________________________________________________________ Support the World Aids Awareness campaign this month with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/
Hi people, Are any of you lot handy with pgtcl then? Or should I be posting on another list, the only other list I could possible see was pgsql-hackers? I managed to modify the tcl script at the address I posted below to save the field I wanted it to. However I've still not managed to get it to save the names and values of all my primary keys. All I've managed to do is create a script that does nothing. I've attached it, could someone help? Thanks Glyn --- Glyn Astill <glynastill@yahoo.co.uk> wrote: > Hi people, > > I've been trying to set up table auditing using a tcl function and > a > trigger. I followed the guide here to start with: > > http://www.alberton.info/postgresql_table_audit.html > > The thing is we have multiple fields in our primary keys so I need > to > change it to handle them all. I was thinking for now to just set > pk_name like "fieldName1,field2Name" and pk_value to > "fieldvalue1,fieldvalue2" etc. > > The script runs the query: > > "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'" > > which should produce a list of the values. It then does: > > #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; > } > } > > Which I presume just gets the first value, then breaks. I want to > get > them all and put them into a comma separated string. So (baering in > mind I've not touched tcl before) I changed this to: > > #get PK value > foreach field $TG_relatts { > if {[string equal -nocase [lindex [array get NEW $field] 0] > $pk_name]} { > if {[string length $pk_value] > 0} { > append pk_value "," [lindex [array get NEW $field] 1] > append pk_list "," $pk_name > } else { > set pk_value [lindex [array get NEW $field] 1] > set pk_list $pk_name > } > #break; > } > } > > But it didn't work, so I guess I'm missing something. > > > > > > > > > ___________________________________________________________ > Support the World Aids Awareness campaign this month with Yahoo! > For Good http://uk.promotions.yahoo.com/forgood/ > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend > ___________________________________________________________ Support the World Aids Awareness campaign this month with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/
Вложения
Also are there any better ways to debug tcl scripts in postgres? I've just noticed the script I'm using (and the one on the example site) works for a single row delete, but not for more than 1 row. It's hard to see from the error given in postgres what's going off. E.g. DELETE FROM "MYTABLE" WHERE "ID" = 1 Deletes one row, and updates my audit table, but DELETE FROM "MYTABLE" WHERE "ID" >= 1 Deletes the rows, but gives an error on logging the audit table. Anyone got any ideas on good ways of auditing table opperations? --- Glyn Astill <glynastill@yahoo.co.uk> wrote: > Hi people, > > Are any of you lot handy with pgtcl then? Or should I be posting on > another list, the only other list I could possible see was > pgsql-hackers? > > I managed to modify the tcl script at the address I posted below to > save the field I wanted it to. However I've still not managed to > get > it to save the names and values of all my primary keys. All I've > managed to do is create a script that does nothing. > > I've attached it, could someone help? > > Thanks > Glyn > > > --- Glyn Astill <glynastill@yahoo.co.uk> wrote: > > > Hi people, > > > > I've been trying to set up table auditing using a tcl function > and > > a > > trigger. I followed the guide here to start with: > > > > http://www.alberton.info/postgresql_table_audit.html > > > > The thing is we have multiple fields in our primary keys so I > need > > to > > change it to handle them all. I was thinking for now to just set > > pk_name like "fieldName1,field2Name" and pk_value to > > "fieldvalue1,fieldvalue2" etc. > > > > The script runs the query: > > > > "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'" > > > > which should produce a list of the values. It then does: > > > > #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; > > } > > } > > > > Which I presume just gets the first value, then breaks. I want to > > get > > them all and put them into a comma separated string. So (baering > in > > mind I've not touched tcl before) I changed this to: > > > > #get PK value > > foreach field $TG_relatts { > > if {[string equal -nocase [lindex [array get NEW $field] 0] > > $pk_name]} { > > if {[string length $pk_value] > 0} { > > append pk_value "," [lindex [array get NEW $field] 1] > > append pk_list "," $pk_name > > } else { > > set pk_value [lindex [array get NEW $field] 1] > > set pk_list $pk_name > > } > > #break; > > } > > } > > > > But it didn't work, so I guess I'm missing something. > > > > > > > > > > > > > > > > > > ___________________________________________________________ > > Support the World Aids Awareness campaign this month with Yahoo! > > For Good http://uk.promotions.yahoo.com/forgood/ > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 6: explain analyze is your friend > > > > > > ___________________________________________________________ > Support the World Aids Awareness campaign this month with Yahoo! > For Good http://uk.promotions.yahoo.com/forgood/> > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend > ___________________________________________________________ Support the World Aids Awareness campaign this month with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/