Postgres Pro
Downloads
Home   >   mailing lists

Thread: triggers: how to check if a field changed?

triggers: how to check if a field changed?

From
Date:
hi list
 
for our discussion board i've successfully set up a trigger to have tsearch2 index the record every time it is updated. this works fine, but also triggers the reindex of the record every time i update the "# of views" counter, which creates unnecessary load. in a trigger, is there a way to check the fields affected by the UPDATE query? i only want to have the tsearch2 index updated when the indexed field (p_msg_raw) changed...
 
this is the current trigger (straight following the tsearch2 guide):
 
CREATE TRIGGER "posts_ts_update" BEFORE INSERT OR UPDATE
ON "forum"."posts" FOR EACH ROW
EXECUTE PROCEDURE "public"."tsearch2"(idxfti, p_msg_raw);
 
thanks in advance,
thomas

Re: triggers: how to check if a field changed?

From
"Lance Arlaus"
Date:
The following assumes you're writing the trigger in PL/pgSQL, but there are similar facilities in the other supported langs.
 
There are two records (OLD and NEW) that are pre-defined in trigger procedures.  Simply compare the fields on those records.
For example (assuming field is non-nullable):
 
IF (OLD.p_msg_raw <> NEW.p_msg_raw) THEN
    -- Reindex
ENDIF
 

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of me@alternize.com
Sent: Monday, August 29, 2005 8:47 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] triggers: how to check if a field changed?

hi list
 
for our discussion board i've successfully set up a trigger to have tsearch2 index the record every time it is updated. this works fine, but also triggers the reindex of the record every time i update the "# of views" counter, which creates unnecessary load. in a trigger, is there a way to check the fields affected by the UPDATE query? i only want to have the tsearch2 index updated when the indexed field (p_msg_raw) changed...
 
this is the current trigger (straight following the tsearch2 guide):
 
CREATE TRIGGER "posts_ts_update" BEFORE INSERT OR UPDATE
ON "forum"."posts" FOR EACH ROW
EXECUTE PROCEDURE "public"."tsearch2"(idxfti, p_msg_raw);
 
thanks in advance,
thomas

Re: triggers: how to check if a field changed?

From
Date:
well, i wonder if this is really performant on large TEXT fields or if the compare costs as much as just running reindex for every update...
 
- thomas 
 
----- Original Message -----
Sent: Tuesday, August 30, 2005 6:25 AM
Subject: Re: [NOVICE] triggers: how to check if a field changed?

The following assumes you're writing the trigger in PL/pgSQL, but there are similar facilities in the other supported langs.
 
There are two records (OLD and NEW) that are pre-defined in trigger procedures.  Simply compare the fields on those records.
For example (assuming field is non-nullable):
 
IF (OLD.p_msg_raw <> NEW.p_msg_raw) THEN
    -- Reindex
ENDIF
 

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of me@alternize.com
Sent: Monday, August 29, 2005 8:47 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] triggers: how to check if a field changed?

hi list
 
for our discussion board i've successfully set up a trigger to have tsearch2 index the record every time it is updated. this works fine, but also triggers the reindex of the record every time i update the "# of views" counter, which creates unnecessary load. in a trigger, is there a way to check the fields affected by the UPDATE query? i only want to have the tsearch2 index updated when the indexed field (p_msg_raw) changed...
 
this is the current trigger (straight following the tsearch2 guide):
 
CREATE TRIGGER "posts_ts_update" BEFORE INSERT OR UPDATE
ON "forum"."posts" FOR EACH ROW
EXECUTE PROCEDURE "public"."tsearch2"(idxfti, p_msg_raw);
 
thanks in advance,
thomas