pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedtrigger problem
3+ messages / 3 participants
[nested] [flat]
* trigger problem
@ 2000-10-15 03:51 Horst Herb <hherb@malleenet.net.au>
0 siblings, 0 replies; 3+ messages in thread
From: Horst Herb @ 2000-10-15 03:51 UTC (permalink / raw)
To: 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();
^ permalink raw reply [nested|flat] 3+ messages in thread
* Trigger problem
@ 2001-03-17 05:22 jreniz <jreniz@tutopia.com>
0 siblings, 1 reply; 3+ messages in thread
From: jreniz @ 2001-03-17 05:22 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org.pgsql-admin
Hi friends!
I'm working in a trigger and I need to put the result
of a query into a variable.
That's very easy- apparently!
The query has a aggregate function like this:
select sum(field) into variable ...
and I'm sure that field and variable are int4 type.
So, when I run this trigger there is a mistake:
''there is no operator '=$' for types 'int4' and 'int4'
you will either have to retype this query using an
explicit cast, or you will have to define the operator
using CREATE OPERATOR''
what's meaning this? and
how can I assign the result of aggregate function into a
variable?
(My system is 6.5.3)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trigger problem
@ 2001-03-18 20:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: jreniz <jreniz@tutopia.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2001-03-18 20:37 UTC (permalink / raw)
To: jreniz <jreniz@tutopia.com>; +Cc: pgsql-hackers@postgreSQL.org
jreniz <jreniz@tutopia.com> writes:
> So, when I run this trigger there is a mistake:
> ''there is no operator '=$' for types 'int4' and 'int4'
> (My system is 6.5.3)
This is an old bug. Update to 7.0.3.
It might work to add spaces around the '=' signs in your trigger
function, but an update would be a good idea anyway.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-03-18 20:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-15 03:51 trigger problem Horst Herb <hherb@malleenet.net.au>
2001-03-17 05:22 Trigger problem jreniz <jreniz@tutopia.com>
2001-03-18 20:37 ` Re: Trigger problem Tom Lane <tgl@sss.pgh.pa.us>
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