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
wthoutmodification as long as the table has the attribute "crc", it is still awkward to recreate 300 trigger functions
callingthis 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
thetrigger 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
BEFOREINSERT 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 contentsset cstr "";set len [llength $TG_relatts];for
{seti 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
checksumspi_exec "select crc32(''$cstr'') as crcs";# update the new recordset NEW(crc) $crcs;#log the insertspi_exec
"insertinto 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();