trigger problem

From: "Horst Herb" <hherb(at)malleenet(dot)net(dot)au>
To: <pgsql-interfaces(at)postgresql(dot)org>
Subject: trigger problem
Date: 2000-10-15 03:51:16
Message-ID: 01af01c0365b$34617560$e7d2fea9@esmith.midgard
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-interfaces

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();

Browse pgsql-interfaces by date

  From Date Subject
Next Message Oliver Fischer 2000-10-15 18:19:17 PQclear() causes coredump
Previous Message george stewart 2000-10-14 20:09:16 ANN: Osage-1.0pre9 available