Which SQL statement can I use to re-execute the trigger and update all the rows in a table?

Question

I have a table with a lot of rows and I have changed the function associated with the trigger in insert or update events. This function makes calculations to update columns with conditional logic according to the values in two or more cells, so an single update statement would not be useful.

So, which SQL statement can I use to re-execute the trigger and update all the rows?

Thank you.

Answer 1

You have to update a column with exactly the same value. Of course it depends on the conditions for trigger. For instance:

UPDATE table
  SET columnX = columnX;

Any way, as a best-practices for this cases I usually have and associated function in which I can run something like (not sure if works for progresql):

BEGIN
  for c in (select column_id from table)
  loop
    function_on_trigger(c.column_id);
  end loop;
END;
/

Answer 2

In order to get the trigger to fire you have to perform the DML that calls the trigger, e.g. I understand in your case perform an update. One option to consider is to perform an update that doesn't actually change anything. I must admit I've never tried this but it would be the first thing I would try (in a test instance of course)

   Update [table]
   set [column_1] = [column_1]

Alternately you might consider directly calling the function in an update statement. Either disable the trigger when doing this or accept that there will be about twice the work done as actually required.

   update [table]
   set [column to change] = [function call]

Test both options first in a test instance or (not recommended approach) by copying the table (structure and data) and trigger to a new table and perform the action on the new table first.

Answer 3

In response to FerranB's answer, the correct way to run the function in Postgres would be the following:

SELECT function_on_trigger(column_id) FROM table;