> On 26 Oct 2021, at 9:05, Mitar <mmitar@gmail.com> wrote:
>
> Hi!
>
> I have a trigger like:
>
> CREATE TRIGGER update_trigger AFTER UPDATE ON my_table REFERENCING NEW
> TABLE AS new_table OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE
> FUNCTION trigger_function;
>
> 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
Perhaps if you store an extra column containing a hash (for example MD5) of the row contents (minus the hash column,
obviously)?You can put an index on the hash and match between OLD and NEW tables which ones changed.
When calculating the hash, you would have to specify the column names to exclude the hash itself, so something like
this:
md5(row(col1, col2, col3)::text)
The row-to-text conversion already takes care of converting JSONB(!) to text.
Don’t use this approach with JSON (as opposed to JSONB) type fields though, a single extra space in the JSON structure
wouldalready lead to a difference, as would other formatting differences.
Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.