From: | Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Problem with PgTcl auditing function on trigger |
Date: | 2008-01-04 10:50:20 |
Message-ID: | 707664.7749.qm@web25801.mail.ukl.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin 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..."
__________________________________________________________
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com
Attachment | Content-Type | Size |
---|---|---|
Audit_recnums2.tcl | application/octet-stream | 2.8 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Suresh Gupta VG | 2008-01-04 11:24:37 | Need source for Solaris 9 |
Previous Message | Scott Marlowe | 2008-01-04 10:49:48 | Re: Need some info on Postgresql |
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2008-01-04 10:59:43 | Re: Dynamic Partitioning using Segment Visibility Maps |
Previous Message | Simon Riggs | 2008-01-04 10:40:17 | Re: Dynamic Partitioning using Segment Visibility Maps |