Determine in a trigger if UPDATE query really changed anything

From: Mitar <mmitar(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Determine in a trigger if UPDATE query really changed anything
Date: 2018-12-24 04:21:22
Message-ID: CAKLmikOsZjXwP+wQXvkuTQO-xTnfP1W7_U3uj3LDwpU3c6oopw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

Currently I am doing:

CREATE TRIGGER some_trigger AFTER UPDATE ON my_table REFERENCING NEW
TABLE AS new_table OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE
FUNCTION my_trigger();

In my trigger I do:

PERFORM * FROM ((TABLE new_table EXCEPT TABLE new_table) UNION ALL
(TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1;
IF FOUND THEN
...

But I wonder if there is an easier way. I would just like to know if
an UPDATE really changed anything.

For DELETE I do "PERFORM * FROM old_table LIMIT 1" and for INSERT I
do "PERFORM * FROM new_table LIMIT 1" and I think this is reasonable.
Still, not sure why I have to store the whole relation just to know if
statement really changed anything.

Mitar

--
http://mitar.tnode.com/
https://twitter.com/mitar_m

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mitar 2018-12-24 07:06:51 Re: Watching for view changes
Previous Message Igor Korot 2018-12-24 03:37:29 NOTIFY/LISTEN with ODBC interface