Re: Table auditing / Pg/tcl help

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(at)yahoo(dot)co(dot)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(at)yahoo(dot)co(dot)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/