Mitar schrieb am 26.10.2021 um 09:05:
> I would like to test inside trigger_function if the table really
> changed. I have tried to do:
>
> PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL
> (TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1;
> IF FOUND THEN
> ... changed ...
> END IF;
>
> But this fails if the table contains a JSON field with the error:
>
> could not identify an equality operator for type json
>
If you use jsonb (which is recommended over json anyway), then this
would work.
I would probably use a row level trigger instead of a statement level trigger
Then compare the two records using IS DISTINCT FROM
if new is distinct from old then
....
end if;
> I want to skip trivial updates (those which have not changed anything).
It seems you might want to use the pre-defined function suppress_redundant_updates_trigger()
https://www.postgresql.org/docs/current/functions-trigger.html
Might be faster than a self written trigger.
Regards
Thomas