> > > http://www.l-t.ee/marko/pgsql/pgcrypto-0.1.tar.gz (11k)
First of all, thankd for tis contribution. I had impemented a similar thing for my own
purposes. A problem I still have is using the digest for "checksumming" rows in my
tables - which is a 'MUST' for medico-legal reasons in my case. I use the follwing
trigger and function (just a proof of concept implementation)
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 strong hash
spi_exec "select pg_crc32(''$cstr'') as crcs";
# update the new record
set NEW(crc) $crcs;
#spi_exec "insert into logger(crc) values (''$crcs'')";
return [array get NEW]
' LANGUAGE 'pltcl';
CREATE TRIGGER trig_crc
BEFORE INSERT OR UPDATE ON crclog
FOR EACH ROW
EXECUTE PROCEDURE trigfunc_crc();
----------------------------------------------------------------------
As you can see, the trigfunc_crc is fairly generic and will work with any table
containing the attribute "crc".
Have you found a way of
- making the trigger generic as well (I hate to rebuild all triggers for 300+ tables
whenever I modify trigfunc_crc)
- any better performing way to implement trigfunc_crc ?
Horst
