0

So, 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

Does anyone have any idea why this error is 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 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 at 3:32
0

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.

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.