public inbox for [email protected]help / color / mirror / Atom feed
PostgreSQL trigger how to detect a column value explicitely modified 3+ messages / 3 participants [nested] [flat]
* PostgreSQL trigger how to detect a column value explicitely modified @ 2025-11-04 12:48 PALAYRET Jacques <[email protected]> 2025-11-04 15:00 ` Re: PostgreSQL trigger how to detect a column value explicitely modified Dominique Devienne <[email protected]> 2025-11-04 15:08 ` Re: PostgreSQL trigger how to detect a column value explicitely modified Tom Lane <[email protected]> 0 siblings, 2 replies; 3+ messages in thread From: PALAYRET Jacques @ 2025-11-04 12:48 UTC (permalink / raw) To: [email protected] 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 c 2 =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 UPDATE tb SET c2=2 ; TABLE tb ; id | c2 | c3 -----+----+---- 100 | 0 | 2 ---------- ---------- ---------- ---------- ---------- UPDATE tb SET c 3 =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 Regards ----- Météo-France ----- PALAYRET Jacques DCSC/GDC [email protected] Fixe : +33 561078319 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL trigger how to detect a column value explicitely modified 2025-11-04 12:48 PostgreSQL trigger how to detect a column value explicitely modified PALAYRET Jacques <[email protected]> @ 2025-11-04 15:00 ` Dominique Devienne <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Dominique Devienne @ 2025-11-04 15:00 UTC (permalink / raw) To: PALAYRET Jacques <[email protected]>; +Cc: [email protected] On Tue, Nov 4, 2025 at 1:49 PM PALAYRET Jacques <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL trigger how to detect a column value explicitely modified 2025-11-04 12:48 PostgreSQL trigger how to detect a column value explicitely modified PALAYRET Jacques <[email protected]> @ 2025-11-04 15:08 ` Tom Lane <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Tom Lane @ 2025-11-04 15:08 UTC (permalink / raw) To: PALAYRET Jacques <[email protected]>; +Cc: [email protected] PALAYRET Jacques <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-11-04 15:08 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-11-04 12:48 PostgreSQL trigger how to detect a column value explicitely modified PALAYRET Jacques <[email protected]> 2025-11-04 15:00 ` Re: PostgreSQL trigger how to detect a column value explicitely modified Dominique Devienne <[email protected]> 2025-11-04 15:08 ` Re: PostgreSQL trigger how to detect a column value explicitely modified Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox