Trigger function does not exist, but I am pretty sure it does

Question

I have a procedure:

create or replace procedure pro_update_last_read(in input_sensor_id integer, in read_time timestamp)
as
$$
begin
    update sensor
    set last_read = read_time
    where sensor_id = input_sensor_id;
end;
$$ language plpgsql;

And a trigger that calls it:

create trigger tri_last_read
    after insert or update of report_time
    on report
execute procedure pro_update_last_read(sensor_id, report_time);

However, when creating the trigger, I get the error message:

 [42883] ERROR: function pro_update_last_read() does not exist

Why is this error happening?

Answer 1

You must use a function, not a procedure:

CREATE FUNCTION pro_update_last_read() RETURNS trigger ...

The trigger must be defined FOR EACH ROW, and you cannot pass columns to the trigger function.

The way you access the columns in the trigger function is via the NEW variable: NEW.sensor_id and NEW.report_time.

Answer 2

I blame the widespread misnomer "stored procedure" for Postgres functions. This led to the folly of using this syntax to create triggers:

CREATE TRIGGER ... FOR ... EXECUTE PROCEDURE function_name ...

Not sure how much the SQL standard is to blame, speaking of "SQL procedure statements" as trigger actions.
Until Postgres 10, the potential confusion was limited as there where only functions. But Postgres 11 finally introduced true SQL procedures. No wonder some are mislead into providing a procedure there.

More sensible alternative syntax was added in Postgres 11:

CREATE TRIGGER ... FOR ... EXECUTE FUNCTION function_name ...

That's not backward compatible. So it will take some time to take over.

If you don't have to stay compatible with Postgres 10 or older, use the new syntax to remind yourself to use a function - like Laurenz instructed.

Related:

• What are the differences between "Stored Procedures" and "Stored Functions"?

(It was also Peter Eisentraut who eventually implemented stored procedures as well as the new syntax for triggers.)