Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 21E822E35DB for ; Fri, 4 Jan 2008 06:50:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 95971-02-3 for ; Fri, 4 Jan 2008 06:50:23 -0400 (AST) Received: from n1.bullet.mail.mud.yahoo.com (n1.bullet.mail.mud.yahoo.com [209.191.127.221]) by postgresql.org (Postfix) with SMTP id 8694C2E375C for ; Fri, 4 Jan 2008 06:50:23 -0400 (AST) Received: from [209.191.108.97] by n1.bullet.mud.yahoo.com with NNFMP; 04 Jan 2008 10:50:22 -0000 Received: from [209.191.119.163] by t4.bullet.mud.yahoo.com with NNFMP; 04 Jan 2008 10:50:22 -0000 Received: from [127.0.0.1] by omp102.mail.mud.yahoo.com with NNFMP; 04 Jan 2008 10:50:22 -0000 X-Yahoo-Newman-Property: ymail-5 X-Yahoo-Newman-Id: 70480.52424.bm@omp102.mail.mud.yahoo.com Received: (qmail 8338 invoked by uid 60001); 4 Jan 2008 10:50:20 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk; h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=zilZoUMsnQ5RxWs83PlTzs1aMXKf+zIsKTdVPdTxF8vfMf04V042jxLJrcyQUDMGQVlz+3poJBciBsfCZa8hGXl0CdkCTdQvtQ7a65X4GbZKh/WRXiYhN1htrDpcR+qOReVIm/O/+i0ANCSPEYs+K0EUUc6yWRNgbkc3S58+xFw=; X-YMail-OSG: corJcBsVM1k5Bi5Z8nr2wgaVTxB8eMdnyNMb2x0pU3NYIFyBUSbIK3DPiO2jmW2N8tDjiBJxxEtSQ9oBZuw4M5vKhyQjjLGyvtNeK0gaMCobqdMbTCZWuFimW_c4y_azL5bob_Pa79jm9A-- Received: from [83.166.184.142] by web25801.mail.ukl.yahoo.com via HTTP; Fri, 04 Jan 2008 02:50:20 PST Date: Fri, 4 Jan 2008 02:50:20 -0800 (PST) From: Glyn Astill Subject: Problem with PgTcl auditing function on trigger To: pgsql-admin@postgresql.org Cc: pgsql-hackers@postgresql.org MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="0-428137408-1199443820=:7749" Content-Transfer-Encoding: 8bit Message-ID: <707664.7749.qm@web25801.mail.ukl.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/54 X-Sequence-Number: 27828 --0-428137408-1199443820=:7749 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Content-Id: Content-Disposition: inline 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 --0-428137408-1199443820=:7749 Content-Type: application/octet-stream; name="Audit_recnums2.tcl" Content-Transfer-Encoding: base64 Content-Description: 1337167722-Audit_recnums2.tcl Content-Disposition: attachment; filename="Audit_recnums2.tcl" Q1JFQVRFIE9SIFJFUExBQ0UgRlVOQ1RJT04gInB1YmxpYyIuImxvZ190b19h dWRpdF90YWJsZSIgKCkgUkVUVVJOUyB0cmlnZ2VyIEFTDQokYm9keSQNCnNw aV9leGVjICJTRUxFQ1QgQ1VSUkVOVF9VU0VSIEFTIHRndXNlciINCnNwaV9l eGVjICJTRUxFQ1QgcmVsbmFtZSBBUyB0Z25hbWUgRlJPTSBwZ19jbGFzcyBX SEVSRSByZWxmaWxlbm9kZSA9ICRUR19yZWxpZCINCg0KI3NraXAgY2hhbmdl cyBvbiBhdWRpdF90YWJsZQ0KaWYge1tzdHJpbmcgZXF1YWwgLW5vY2FzZSAk dGduYW1lIGF1ZGl0X3RhYmxlXX0geyByZXR1cm4gT0sgfQ0KDQojZ2V0IFBL IG5hbWUNCnNldCBwa19uYW1lICJSRUNOVU0iDQpzZXQgcGtfdmFsdWUgIiIN Cg0Kc3dpdGNoICRUR19vcCB7DQpJTlNFUlQgew0KICANCiAgI2dldCBQSyB2 YWx1ZQ0KICBmb3JlYWNoIGZpZWxkICRUR19yZWxhdHRzIHsNCiAgICBpZiB7 W3N0cmluZyBlcXVhbCAtbm9jYXNlIFtsaW5kZXggW2FycmF5IGdldCBORVcg JGZpZWxkXSAwXSAkcGtfbmFtZV19IHsNCiAgICAgIHNldCBwa192YWx1ZSBb bGluZGV4IFthcnJheSBnZXQgTkVXICRmaWVsZF0gMV0NCiAgICAgIGJyZWFr Ow0KICAgIH0NCiAgfQ0KICAjbG9nIGluc2VydGVkIHJvdyB2YWx1ZXMNCiAg Zm9yZWFjaCBmaWVsZCAkVEdfcmVsYXR0cyB7DQogICAgaWYgeyEgW3N0cmlu ZyBlcXVhbCAtbm9jYXNlIFtsaW5kZXggW2FycmF5IGdldCBORVcgJGZpZWxk XSAwXSAkcGtfbmFtZV19IHsNCiAgICAgIHNldCBtb2RpZmllZF9maWVsZCBb bGluZGV4IFthcnJheSBnZXQgTkVXICRmaWVsZF0gMF0NCiAgICAgIHNldCBj dXJyZW50X3ZhbHVlIFtsaW5kZXggW2FycmF5IGdldCBORVcgJGZpZWxkXSAx XQ0KICAgICAgc3BpX2V4ZWMgLWFycmF5IEMgIklOU0VSVCBJTlRPIGF1ZGl0 X3RhYmxlKHRzLCB1c3IsIHRibCwgZmxkLCBwa19uYW1lLCBwa192YWx1ZSwg bW9kX3R5cGUsIG9sZF92YWwsIG5ld192YWwpDQoJVkFMVUVTIChDVVJSRU5U X1RJTUVTVEFNUCwgJyR0Z3VzZXInLCAnICR0Z25hbWUnLCAnJG1vZGlmaWVk X2ZpZWxkJywgJyRwa19uYW1lJywgJyRwa192YWx1ZScsICckVEdfb3AnLCBO VUxMLCAnJGN1cnJlbnRfdmFsdWUnKSINCiAgICB9DQogIH0NCn0NClVQREFU RSB7DQoNCiAgI2dldCBQSyB2YWx1ZQ0KICBmb3JlYWNoIGZpZWxkICRUR19y ZWxhdHRzIHsNCiAgICBpZiB7W3N0cmluZyBlcXVhbCAtbm9jYXNlIFtsaW5k ZXggW2FycmF5IGdldCBORVcgJGZpZWxkXSAwXSAkcGtfbmFtZV19IHsNCiAg ICAgIHNldCBwa192YWx1ZSBbbGluZGV4IFthcnJheSBnZXQgTkVXICRmaWVs ZF0gMV0NCiAgICAgIGJyZWFrOw0KICAgIH0NCiAgfQ0KICAjbG9nIGluc2Vy dGVkIHJvdyB2YWx1ZXMNCiAgZm9yZWFjaCBmaWVsZCAkVEdfcmVsYXR0cyB7 DQogICAgI2NoZWNrIGNoYW5nZWQgZmllbGRzDQogICAgaWYge1tzdHJpbmcg ZXF1YWwgLW5vY2FzZSBbYXJyYXkgZ2V0IE5FVyAkZmllbGRdIFthcnJheSBn ZXQgT0xEICRmaWVsZF1dID09IDB9IHsNCiAgICAgIHNldCBtb2RpZmllZF9m aWVsZCBbbGluZGV4IFthcnJheSBnZXQgT0xEICRmaWVsZF0gMF0NCiAgICAg IGlmIHtbc3RyaW5nIGNvbXBhcmUgJG1vZGlmaWVkX2ZpZWxkICIiXSA9PSAw fSB7DQoJc2V0IG1vZGlmaWVkX2ZpZWxkIFtsaW5kZXggIFthcnJheSBnZXQg TkVXICRmaWVsZF0gMF0NCiAgICAgIH0NCiAgICAgIHNldCBwcmV2aW91c192 YWx1ZSBbbGluZGV4IFthcnJheSBnZXQgT0xEICRmaWVsZF0gMV0NCiAgICAg IHNldCBjdXJyZW50X3ZhbHVlICBbbGluZGV4IFthcnJheSBnZXQgTkVXICRm aWVsZF0gMV0NCiAgICAgIHNwaV9leGVjIC1hcnJheSBDICJJTlNFUlQgSU5U TyBhdWRpdF90YWJsZSh0cywgdXNyLCB0YmwsIGZsZCwgcGtfbmFtZSwgcGtf dmFsdWUsIG1vZF90eXBlLCBvbGRfdmFsLCBuZXdfdmFsKQ0KCVZBTFVFUyAo Q1VSUkVOVF9USU1FU1RBTVAsICckdGd1c2VyJywgJyR0Z25hbWUnLCAnJG1v ZGlmaWVkX2ZpZWxkJywgJyRwa19uYW1lJywgJyRwa192YWx1ZScsICckVEdf b3AnLCAnJHByZXZpb3VzX3ZhbHVlJywgJyRjdXJyZW50X3ZhbHVlJykiDQog ICAgfQ0KICB9DQp9DQpERUxFVEUgew0KDQogICNnZXQgUEsgdmFsdWUNCiAg Zm9yZWFjaCBmaWVsZCAkVEdfcmVsYXR0cyB7DQogICAgaWYge1tzdHJpbmcg ZXF1YWwgLW5vY2FzZSBbbGluZGV4IFthcnJheSBnZXQgT0xEICRmaWVsZF0g MF0gJHBrX25hbWVdfSB7DQogICAgICBzZXQgcGtfdmFsdWUgW2xpbmRleCBb YXJyYXkgZ2V0IE9MRCAkZmllbGRdIDFdDQogICAgICBicmVhazsNCiAgICB9 DQogIH0NCiAgI2xvZyBpbnNlcnRlZCByb3cgdmFsdWVzDQogIGZvcmVhY2gg ZmllbGQgJFRHX3JlbGF0dHMgew0KICAgIGlmIHshIFtzdHJpbmcgZXF1YWwg LW5vY2FzZSBbbGluZGV4IFthcnJheSBnZXQgT0xEICRmaWVsZF0gMF0gJHBr X25hbWVdfSB7DQogICAgICBzZXQgbW9kaWZpZWRfZmllbGQgW2xpbmRleCBb YXJyYXkgZ2V0IE9MRCAkZmllbGRdIDBdDQogICAgICBzZXQgcHJldmlvdXNf dmFsdWUgW2xpbmRleCBbYXJyYXkgZ2V0IE9MRCAkZmllbGRdIDFdDQogICAg ICBzcGlfZXhlYyAtYXJyYXkgQyAiSU5TRVJUIElOVE8gYXVkaXRfdGFibGUo dHMsIHVzciwgdGJsLCBmbGQsIHBrX25hbWUsIHBrX3ZhbHVlLCBtb2RfdHlw ZSwgb2xkX3ZhbCwgbmV3X3ZhbCkNCglWQUxVRVMgKENVUlJFTlRfVElNRVNU QU1QLCAnJHRndXNlcicsICckdGduYW1lJywgJyRtb2RpZmllZF9maWVsZCcs ICckcGtfbmFtZScsICckcGtfdmFsdWUnLCAnJFRHX29wJywgJyRwcmV2aW91 c192YWx1ZScsIE5VTEwpIg0KICAgIH0NCiAgfQ0KfQ0KfQ0KcmV0dXJuIE9L DQokYm9keSQNCkxBTkdVQUdFICdwbHRjbCcgVk9MQVRJTEUgQ0FMTEVEIE9O IE5VTEwgSU5QVVQgU0VDVVJJVFkgSU5WT0tFUjs= --0-428137408-1199443820=:7749--