Re: How to know a record has been updated, then reset the flag?

Jim Archer <jim(at)archer(dot)net> writes:
> I need to flag a record when it is updated or when it is a new insert.
> Then I SELECT for the changed records and do something not related to
> Postgres. Easy enough, I created a trigger procedure and fired it on
> INSERT OR UPDATE and modify NEW to set the flag field to true.

> But then the problem is how do I reset the trigger? If I do an UPDATE the
> trigger fires again.

I think you need a three-state value instead of a boolean. The trigger
has to account for four cases:
* freshly inserted row (which will have the field's default value)
* newly updated row
* re-updated row (where we don't want to reset the flag)
* update that is supposed to reset the flag
and you simply cannot tell the third and fourth cases apart without
an additional state.

One possibility is to make the flag field be "int default 0", with
trigger logic along the lines of

if new.flag = 0 then
-- freshly inserted or newly updated row, so set flag
new.flag = 1;
elsif new.flag = 1 then
-- re-update, no change needed
elsif new.flag = 2 then
-- command to reset flag
new.flag = 0;
else
-- possibly raise error here
end if;

and obviously the convention for resetting the flag is to attempt to
update it to 2.

(Thinks some more...) Actually you could stick with a boolean field,
if you make use of NULL as your third state --- that is, the convention
becomes that the command for resetting the flag is to attempt to update
it to NULL. However this might be more fragile than the above, since
you can certainly imagine ordinary inserts or updates accidentally doing
it.

regards, tom lane