1

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?

  • 1
    postgresql.org/docs/12/plpgsql-trigger.html- “A data change trigger is declared as a function with no arguments and a return type of trigger. Note that the function must be declared with no arguments even if it expects to receive some arguments specified in CREATE TRIGGER — such arguments are passed via TG_ARGV, as described below.” – SQLRaptor Oct 16 '19 at 3:02
  • Any idea how to fix it? It's for an assignment and it states that pro_update_last_read MUST be a procedure – Cygnini - ShadowRi5ing Oct 16 '19 at 3:32
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.

0

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 people are mislead into providing a procedure there.

Seeing this, the more sensible alternative syntax was added in Postgres 11:

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

But that's not backward compatible. So it will take some time to spread.

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

Related:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.