How to invoke a trigger on all rows manually in postgres

Question

My trigger is defined the following way:

CREATE TRIGGER update_contract_finished_at
    AFTER INSERT OR DELETE OR UPDATE OF performed_on
    ON task
    FOR EACH ROW
    EXECUTE PROCEDURE update_contract_finished_at_function();

I now want to evoke this trigger to set the variables which are updated by the trigger. How do I do that?

Something like

for each row in task
execute procedure update_contract_finished_at_function();

I know I can update with a standard update set statement. I also want to verifiy that my trigger works on all the data correctly.

Answer 1

I'd write a slightly modified copy of update_contract_finished_at_function that takes type task as input and returns void.

Then replace NEW in the trigger function with $1 and call the function like this:

SELECT copy_func(task) FROM task;

If the functions are almost identical, it should be good enough to test the trigget function.

Answer 2

The way to manually trigger your on update trigger once would be:

UPDATE task SET performed_on = performed_on

however depending on how complicated your logic is in there and how many rows you have in the table a separate query might be significantly faster for initializing a large number of rows.

Since you mentioned you want to test the behaviour of your trigger you can clone the table or do a table or database dump and restore the data afterwards. If this is a live system you should instead do a database dump, restore to another system, add your trigger, test it, repeat from restore until you nail it... and only after you're sure it does what you want update the live system with it.