Syntax Error Creating Trigger - Column Specification

Question

What is wrong with the trigger definition below? I intend it to execute the "calculate_dow" function whenever the "date" column is updated or inserted but it generates the following error ...

CREATE TRIGGER tr_calculate_dow
    AFTER UPDATE OR INSERT OF date ON focusblu.events
    FOR EACH ROW
    EXECUTE PROCEDURE calculate_dow(date);

Failed to execute SQL : SQL CREATE TRIGGER tr_calculate_dow AFTER UPDATE OR INSERT OF date ON focusblu.events FOR EACH ROW EXECUTE PROCEDURE calculate_dow(date); failed : ERROR: syntax error at or near "OF" LINE 1: ...IGGER tr_calculate_dow AFTER UPDATE OR INSERT OF date ON... ^

Answer 1

I assume your will update the date on each update so you don't have to specify the datefield.

CREATE TRIGGER tr_calculate_dow
    AFTER UPDATE OR INSERT ON focusblu.events
    FOR EACH ROW
    EXECUTE PROCEDURE calculate_dow(date);

Answer 2

The error says you have a syntax error: You cannot use OF there.

The documentation is pretty clear on exactly what you want to, and even has examples based on your "whenever date column is update" criteria:

https://www.postgresql.org/docs/9.1/static/sql-createtrigger.html

Perhaps you wanted something like this?

CREATE TRIGGER tr_calculate_dow
  AFTER UPDATE ON focusblu.events
  FOR EACH ROW
  WHEN (OLD.date IS DISTINCT FROM NEW.date)
  EXECUTE PROCEDURE calculate_dow(NEW.date);

You'll most likely want to do the same for another INSERT trigger as well and just pick the date.