Re: [HACKERS] Problem with PgTcl auditing function on trigger

2008-01-04 Thread Richard Huxton 

Glyn Astill wrote:

Hi people,
 
 I've tried posting on the general list about this, but I never get

 a
 reply, so I'm trying here.


I think you'll probably have more luck with a TCL list than the PG 
hackers list. However, I've attached some pltcl functions I put together 
ages ago to do this sort of thing. Hopefully that will help you.


--
  Richard Huxton
  Archonet Ltd
-- History Tracking Trigger-Functions
--
BEGIN;

-- tcl_track_history(TABLE-NAME)
--	Set TABLE-NAME when creating the trigger. Will automatically record change 
--	details in tables history/history_detail
--
CREATE OR REPLACE FUNCTION tcl_track_history() RETURNS trigger AS '
	switch $TG_op {
		DELETE {
			if { [llength [array names OLD cid]]  0 } {
set clival $OLD(cid)
			} else {
set clival NULL
			}
			spi_exec INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'D\')
		}
		INSERT {
			if { [llength [array names NEW cid]]  0 } {
set clival $NEW(cid)
			} else {
set clival NULL
			}
			spi_exec INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'I\')
		}
		UPDATE {
			if { [llength [array names OLD cid]]  0 } {
set clival $OLD(cid)
			} else {
set clival NULL
			}
			set inserted_main_history_row false
			foreach {col} $TG_relatts {
# First result seems to be an empty string when stepping through columns
if { $col   } {
	# Check if OLD/NEW contain a value
	if { [llength [array names OLD $col]]  0 } {
		set oldval $OLD($col)
	} else {
		set oldval NULL
	}
	if { [llength [array names NEW $col]]  0 } {
		set newval $NEW($col)
	} else {
		set newval NULL
	}
	if { $oldval != $newval } {
		if { !$inserted_main_history_row } {
			spi_exec INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'U\')
			set inserted_main_history_row true
		}
		spi_exec INSERT INTO history_detail (col,was) VALUES (\'$col\', \'[ quote $oldval ]\')
	}
}
			}
		}
	}
	return OK
' LANGUAGE pltcl;

CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client FOR EACH ROW EXECUTE PROCEDURE tcl_track_history('client');
CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client_keyworkers FOR EACH ROW EXECUTE PROCEDURE tcl_track_history('client_keyworkers');
CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client_notes FOR EACH ROW EXECUTE PROCEDURE tcl_track_history('client_notes');

COMMIT;


BEGIN;

CREATE OR REPLACE FUNCTION tcl_track_answers() RETURNS trigger AS '
	switch $TG_op {
		DELETE {
			if { [llength [array names OLD cid]]  0 } {
set clival $OLD(cid)
			} else {
set clival NULL
			}
			spi_exec INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'D\')
		}
		INSERT {
			if { [llength [array names NEW cid]]  0 } {
set clival $NEW(cid)
			} else {
set clival NULL
			}
			spi_exec INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'I\')
		}
		UPDATE {
			# Get question title into var $qn_title
			spi_exec SELECT \'Q\' || qid || \' - \' || title AS qn_title FROM question WHERE qid = $OLD(qid)

			if { [llength [array names OLD cid]]  0 } {
set clival $OLD(cid)
			} else {
set clival NULL
			}

			# Check if OLD/NEW contain a value
			if { [llength [array names OLD text_val]]  0 } {
set oldval $OLD(text_val)
			} else {
set oldval NULL
			}
			if { [llength [array names NEW text_val]]  0 } {
set newval $NEW(text_val)
			} else {
set newval NULL
			}
			if { $oldval != $newval } {
spi_exec INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'U\')
spi_exec INSERT INTO history_detail (col,was) VALUES (\'$qn_title\', \'[ quote $oldval ]\')
			}
		}
	}
	return OK
' LANGUAGE pltcl;

CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client_answer FOR EACH ROW EXECUTE PROCEDURE tcl_track_answers('client_answer');

UPDATE client_answer SET text_val = 'partially sighted',ts=now() WHERE aid=20;  

COMMIT;

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Problem with PgTcl auditing function on trigger

2008-01-04 Thread Brett Schwarz 
 
 
 - Original Message 
 From: Glyn Astill [EMAIL PROTECTED]
 To: pgsql-hackers@postgresql.org
 Sent: Friday, January 4, 2008 5:23:18 AM
 Subject: [HACKERS] Problem with PgTcl auditing function on trigger
 
 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;
 

Is the Tcl function the same for RECNUM and TRANSNO? I noticed you have
set pk_name RECNUM ...

Not sure if you are changing this when you run the different DELETEs.

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

I'm not sure where the error is coming from, off hand. The only thing
I can think of now is that you may need to [quote] the values or
use spi_execp instead. Perhaps there is a ' in there somewhere causing problems.


As a side note, just some tips (i realize that you got this from the link 
above):

You have several instances of constructs such as:

if {! [string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {

but this really is just $field...so you don't need all of that. You can just do:

if {! [string equal -nocase $field $pk_name]} {


Similiarily, you have this construct
set pk_value [lindex [array get NEW $field] 1]

But you can use this instead:
set pk_value $NEW($field)


And then, this whole block:

  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;
}
  }

Not sure the purpose here, but you should be able to just do:

if {[info exists NEW($pk_name)]} {
   set pk_value $NEW($pk_name)
} else {
   # something went wrong here...need this if there's a chance $pk_name 
might not be there
}

Note also that TG_relatts has an empty element as the first element of the 
list, so this
 if {! [string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} {

may be giving you trouble, since it won't catch the empty element.

so, you could write that particular loop construct as such:
  foreach field [lrange $TG_relatts 1 end] {
if {! [string equal -nocase $field $pk_name]} {
  set modified_field $field
  set previous_value $OLD($field)
  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)
}
  }


You may to throw some [elog]'s in there, to see what's going on as well.

HTH,
--brett



  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore