Postgres Trigger and Function to auto complete table

Question

Can't find the solution to make a trigger to fill the pass and success_rate rows

CREATE TABLE en_testes_automatizados (
dia DATE NOT NULL,
id_projeto integer NOT NULL REFERENCES jira3.en_projeto (id_projeto),
total integer NOT NULL,
pass integer,
fail integer,
success_rate DOUBLE PRECISION,
result_link character);

I need to fill the pass row with the expression (total - pass) and the success_rate with ((pass/total) x100)

Answer 1

It seems a little long winded just to auto update a column but first you will need to create a function that the trigger will run using something like this that will set the value of that column

CREATE OR REPLACE FUNCTION en_testes_automatizados_success_rate()
RETURNS TRIGGER AS 
$$
BEGIN
    NEW.success_rate := (pass / (pass + fail)) * 100;

    RETURN NEW;
END
$$
LANGUAGE PLPGSQL;

Then create a trigger for the table

CREATE TRIGGER ON en_testes_automatizados
BEFORE INSERT FOR EACH ROW
EXECUTE PROCEDURE en_testes_automatizados_success_rate();