From: Horst Herb <hherb@malleenet.net.au>
To: pgsql-interfaces@postgresql.org
Subject: trigger problem
Date: Sun, 15 Oct 2000 13:51:16 +1000
Message-ID: <01af01c0365b$34617560$e7d2fea9@esmith.midgard> (raw)
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();
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: hherb@malleenet.net.au
Subject: Re: trigger problem
In-Reply-To: <01af01c0365b$34617560$e7d2fea9@esmith.midgard>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox