Message-ID: <01af01c0365b$34617560$e7d2fea9@esmith.midgard>
Reply-To: "Horst Herb" <horst@hherb.com>
From: "Horst Herb" <hherb@malleenet.net.au>
To: <pgsql-interfaces@postgresql.org>
Subject: trigger problem
Date: Sun, 15 Oct 2000 13:51:16 +1000
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

I need a function that calculates a check sum on all attributes of a newly inserted column of virtually all of my now 300+ tables.

Below an example how I do it now; although my pltcl function "trigfunc_crc" is generic, that is it works for any table wthout modification as long as the table has the attribute "crc", it is still awkward to recreate 300 trigger functions calling this function whenever I modify trigfunc_crc().

1.) Any plans to change this odd behaviour of postgres? (that is, this need for recreating all triggers after modifying the trigger function)?

2.) Anybody who knows a more efficient way than the rather slow pltcl function below?

3.) anybody who knows a way to avoid writing the trigger for each individual table? (like a CREATE TRIGGER trig_crc BEFORE INSERT ON * ..."

DROP TRIGGER trig_crc ON crclog;
DROP FUNCTION trigfunc_crc();

CREATE FUNCTION trigfunc_crc()
RETURNS OPAQUE as '
 # create a string by concatenating all field contents
 set cstr "";
 set len [llength $TG_relatts];
 for {set i 1}  {$i < $len} {incr i} {
  set istr [lindex $TG_relatts $i]
  # skip the crc field!
  if {[string compare "crc" $istr] == 0} continue; 
  # beware of NULL fields
  if [catch {set cstr $cstr$NEW($istr)}] continue; 
 }  
 # calculate the check sum
 spi_exec "select crc32(''$cstr'') as crcs";
 # update the new record
 set NEW(crc) $crcs;
 #log the insert
 spi_exec "insert into insertlog(inserted_attributes, crc) values (''$crcs'', "$cstr"))";
return [array get NEW]  
' LANGUAGE 'pltcl';   

CREATE TRIGGER trig_crc
BEFORE INSERT OR UPDATE ON crclog
FOR EACH ROW
EXECUTE PROCEDURE trigfunc_crc();