Pass the query result to the function-CodePudding

I created a function that takes as a parameter a string by which i am looking for the desired element in the Bus table. After that i create a trigger that will fire after inserting into the Maintenance table. Here i have a problem: i specify that when changing the table, call the function and pass the last added element there, but the trigger is not created.

I looked for similar questions and saw that you need to take the query in brackets, but it did not help.

Ask for your help!

Function:

create function set_status(model_ varchar(50)) returns void as $$
    update Bus set technical_condition = 'don`t work' where model = model_;
    $$ LANGUAGE sql;

Trigger:

create trigger check_insert
    after insert on Maintenance
    for each row
    execute procedure set_status((select model from Maintenance order by id_m desc limit 1));

CodePudding user response：

First off your trigger function must be of the form:

create or replace function <function_name>() 
   returns trigger  
  language plpgsql
as $$
begin 
    ... 
end;
$$;

The language specification may come either before the code or after it. Moreover it must be defined returning trigger and as taking no parameters. See documentation.

You can achieve what you want by moving the select status ... query into the trigger function itself.

create or replace function set_status() 
  returns trigger
 language plpgsql
as $$
begin 
    update bus 
       set technical_condition =  
           (select model 
              from maintenance 
              order by id_m desc 
              limit 1
           ) ;
     return null;
end; 
$$; 

create trigger check_insert
    after insert on maintenance
    for each row
    execute procedure set_status();

NOTE: Not Tested.