trigger problem
От | Horst Herb |
---|---|
Тема | trigger problem |
Дата | |
Msg-id | 01af01c0365b$34617560$e7d2fea9@esmith.midgard обсуждение исходный текст |
Список | 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 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();
В списке pgsql-interfaces по дате отправления: