Re: Determine in a trigger if UPDATE query really changed anything

From: George Neuner <gneuner2(at)comcast(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Determine in a trigger if UPDATE query really changed anything
Date: 2018-12-24 08:31:03
Message-ID: 1q512ehjvtvknghlkrjt2afjlo166mq6cl@4ax.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Sun, 23 Dec 2018 20:21:22 -0800, Mitar <mmitar(at)gmail(dot)com> wrote:

>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.

You're still thinking in terms of deltas for the whole table. Think
individual rows instead.

With a BY ROW trigger, the difference between the NEW and OLD
variables lets you see the changes to the particular row.

George

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mitar 2018-12-24 08:46:39 Re: Determine in a trigger if UPDATE query really changed anything
Previous Message George Neuner 2018-12-24 08:20:36 Re: Watching for view changes