Re: PostgreSQL trigger how to detect a column value explicitely modified

On Tue, Nov 4, 2025 at 1:49 PM PALAYRET Jacques
<jacques(dot)palayret(at)meteo(dot)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