Обсуждение: PostgreSQL trigger how to detect a column value explicitely modified
Hello,
In a trigger body, is there a simple way to know if a column value has been explicitely modified ?
Explicitely modified ; in others words, typically indicated in the SET clause of the UPDATE.
A simple way ; I mean without analysing the SQL statement which can be very difficult according to the statement.
# My trigger function executed by trigger BEFORE UPDATE ON a table tb (with columns id, c2, c3 ; of integer type) FOR EACH ROW :
CREATE OR REPLACE FUNCTION func_tg_upd()
RETURNS trigger LANGUAGE plpgsql
AS $function$
BEGIN
if NEW.c2 != 1
then
NEW.c2 := 0 ;
end if;
return NEW;
END;
$function$
;
# Actions of the trigger
UPDATE tb
SET c2=2
WHERE ... -- old c2 value can be 2 or another value
;
=> c2 will be changed to 0 by the trigger, OK, because I explicitely indicated the new value of c2 to a value different from 1.
The the old value of c2 could be different or not.
In a trigger body, is there a simple way to know if a column value has been explicitely modified ?
Explicitely modified ; in others words, typically indicated in the SET clause of the UPDATE.
A simple way ; I mean without analysing the SQL statement which can be very difficult according to the statement.
# My trigger function executed by trigger BEFORE UPDATE ON a table tb (with columns id, c2, c3 ; of integer type) FOR EACH ROW :
CREATE OR REPLACE FUNCTION func_tg_upd()
RETURNS trigger LANGUAGE plpgsql
AS $function$
BEGIN
if NEW.c2 != 1
then
NEW.c2 := 0 ;
end if;
return NEW;
END;
$function$
;
# Actions of the trigger
UPDATE tb
SET c2=2
WHERE ... -- old c2 value can be 2 or another value
;
=> c2 will be changed to 0 by the trigger, OK, because I explicitely indicated the new value of c2 to a value different from 1.
The the old value of c2 could be different or not.
Example :
TABLE tb ;
id | c2 | c3
-----+----+----
100 | 1 | 2
id | c2 | c3
-----+----+----
100 | 1 | 2
UPDATE tb
SET c2=2
;
TABLE tb ;
id | c2 | c3
-----+----+----
100 | 0 | 2
---------- ---------- ---------- ---------- ----------
UPDATE tb
SET c3=3
WHERE ... -- and c2=2 (previous value of c2 is 2)
;
=> c2 will be changed to 0 by the trigger, but I don't want that action (NOT OK for me) because I didn't explicitely indicate the new value 2 to c2.
Example :
UPDATE tb
SET c3=3
WHERE ... -- and c2=2 (previous value of c2 is 2)
;
=> c2 will be changed to 0 by the trigger, but I don't want that action (NOT OK for me) because I didn't explicitely indicate the new value 2 to c2.
Example :
TABLE tb ;
id | c2 | c3
-----+----+----
100 | 2 | 2
UPDATE tb
SET c3=3
;
TABLE tb ;
id | c2 | c3
-----+----+----
100 | 0 | 3
id | c2 | c3
-----+----+----
100 | 2 | 2
UPDATE tb
SET c3=3
;
TABLE tb ;
id | c2 | c3
-----+----+----
100 | 0 | 3
Regards
----- Météo-France -----
PALAYRET Jacques
DCSC/GDC
jacques.palayret@meteo.fr
Fixe : +33 561078319
PALAYRET Jacques
DCSC/GDC
jacques.palayret@meteo.fr
Fixe : +33 561078319
Re: PostgreSQL trigger how to detect a column value explicitely modified
От
Dominique Devienne
Дата:
On Tue, Nov 4, 2025 at 1:49 PM PALAYRET Jacques
<jacques.palayret@meteo.fr> wrote:
> In a trigger body, is there a simple way to know if a column value has been explicitely modified ?
Using pg_trigger_depth(), you can know whether the trigger is called
from "outer SQL" directly,
or from SQL done within another trigger (because the depth will be
larger). I didn't quite follow
your description, to be honest, but I suspect the above is what you
want (maybe :)). --DD
PS: To illustrate, we have this trigger to enforce some of our tables
are "trigger managed",
and no DMLs should be done "directly" on them (only from triggers). FWIW. --DD
PPS: pg_trigger_depth() is 0 if the trigger function is called
directly (unusual).
1 if directly called from an "outer SQL" statement (from a proc/func or not).
2 or more if triggered from SQL done by another (possibly the same)
"triggered" trigger.
CREATE FUNCTION trigger_managed_tf()
RETURNS TRIGGER
AS $$
BEGIN
IF pg_trigger_depth() < 2 THEN
RAISE EXCEPTION 'Direct insert/update/delete are not allowed
on the % table.', TG_TABLE_NAME;
END IF;
RETURN COALESCE (NEW, OLD);
END
$$ LANGUAGE plpgsql
PALAYRET Jacques <jacques.palayret@meteo.fr> writes:
> In a trigger body, is there a simple way to know if a column value has been explicitely modified ?
> Explicitely modified ; in others words, typically indicated in the SET clause of the UPDATE.
I believe that an ON UPDATE trigger coded in C can access a bitmapset
that shows which column(s) are targeted in the SET clause; but we've
not exposed that to PL/pgSQL or other higher-level languages.
There are of course a bunch of definitional issues. Should
"UPDATE ... SET x = x" count as an update? What if some earlier
(... or later ...) BEFORE trigger changes a column? We don't
provide any help for those cases either.
I think most people settle for testing "OLD.col IS DISTINCT FROM
NEW.col", which you could argue is a good operational definition
of whether the column changed.
regards, tom lane
On Tue, 2025-11-04 at 12:48 +0000, PALAYRET Jacques wrote: > In a trigger body, is there a simple way to know if a column value has been explicitely modified ? > Explicitely modified ; in others words, typically indicated in the SET clause of the UPDATE. > A simple way ; I mean without analysing the SQL statement which can be very difficult according to the statement. Unless you want to write a C trigger function you can use CREATE TRIGGER ... BEFORE|AFTER UPDATE OF col ON tab FOR EACH ROW ... Then the trigger function will only be called if the SET clause of UPDATE contains the column "col". Yours, Laurenz Albe
Hello, I think you knew that I don't really want to create a function in language C to handle and test the values of the columnsin the SQL query. For me, it is much easier to use the solution of the trigger " BEFORE|AFTER UPDATE OF col ". Joke aside, I hadn't thought of that ; it is an excellent idea (simple solution). I tested it ; it works as expected. Thanks to people who replied. Regards. ----- Mail original ----- De: "Laurenz Albe" <laurenz.albe@cybertec.at> À: "PALAYRET Jacques" <jacques.palayret@meteo.fr>, pgsql-general@lists.postgresql.org Envoyé: Mardi 4 Novembre 2025 18:29:05 Objet: Re: PostgreSQL trigger how to detect a column value explicitely modified On Tue, 2025-11-04 at 12:48 +0000, PALAYRET Jacques wrote: > In a trigger body, is there a simple way to know if a column value has been explicitely modified ? > Explicitely modified ; in others words, typically indicated in the SET clause of the UPDATE. > A simple way ; I mean without analysing the SQL statement which can be very difficult according to the statement. Unless you want to write a C trigger function you can use CREATE TRIGGER ... BEFORE|AFTER UPDATE OF col ON tab FOR EACH ROW ... Then the trigger function will only be called if the SET clause of UPDATE contains the column "col". Yours, Laurenz Albe