Skip site navigation (1) Skip section navigation (2)

Problem with PgTcl auditing function on trigger

From: Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Problem with PgTcl auditing function on trigger
Date: 2008-01-04 13:23:18
Message-ID: 674288.75502.qm@web25814.mail.ukl.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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/

Attachment: Audit_recnums2.tcl
Description: application/octet-stream (2.8 KB)

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2008-01-04 13:40:03
Subject: Re: Dynamic Partitioning using Segment Visibility Maps
Previous:From: Markus SchiltknechtDate: 2008-01-04 12:49:27
Subject: Re: Dynamic Partitioning using Segment Visibility Maps

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group