pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Glyn Astill <glynastill@yahoo.co.uk>
To: pgsql-admin@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Problem with PgTcl auditing function on trigger
Date: Fri, 4 Jan 2008 02:50:20 -0800 (PST)
Message-ID: <707664.7749.qm@web25801.mail.ukl.yahoo.com> (raw)

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..."




      __________________________________________________________
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com

Attachments:

  Audit_recnums2.tcl (application/octet-stream, 2.8K) download

Message-ID: <707664.7749.qm@web25801.mail.ukl.yahoo.com>
Permalink:  ../707664.7749.qm@web25801.mail.ukl.yahoo.com/
Also on:    postgresql.org/message-id/707664.7749.qm@web25801.mail.ukl.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: glynastill@yahoo.co.uk, pgsql-hackers@postgresql.org
  Subject: Re: Problem with PgTcl auditing function on trigger
  In-Reply-To: <707664.7749.qm@web25801.mail.ukl.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox