Drizzle Team•9mo ago

trying to create a trigger on supabase to calculate a winrate based on two other columns

hi all, tsuki here. i'm trying to create a trigger that fires before updates, that should calculate a win percentage based on my wins column and my losses column. the trigger is firing correctly, but it seems like it isn't correctly calculating the win percentage? i expected the attached image to have winrate as 60.00 or 60 given the calculation of 6 / (6 + 4) * 100. the code used to create the trigger in the sql editor on supabase some of which was written by the supabase ai helper.

create
or replace function calculate_winrate () returns trigger as $$
begin
    new.winrate = (new.wins / (new.wins + new.losses)) * 100;
    return new;
end;
$$ language plpgsql;

create
or replace trigger calculate_winrate_trigger
before
update on "mtg-player_deck" for each row
execute function calculate_winrate ();

create
or replace function calculate_winrate () returns trigger as $$
begin
    new.winrate = (new.wins / (new.wins + new.losses)) * 100;
    return new;
end;
$$ language plpgsql;

create
or replace trigger calculate_winrate_trigger
before
update on "mtg-player_deck" for each row
execute function calculate_winrate ();

any comments or help is greatly appreciated :JinxHeart:

Solution:

you are dividing two ints - you get an int as a result(rounded down). 6 / (6 + 4) * 100 -> 6 / 10 * 100 -> 0 * 100 multiply new.wins by 1.0 (not just 1) - should help. or do a coalesce to float on one of columns new.winrate = ((1.0 * new.wins) / (new.wins + new.losses)) * 100;...

Jump to solution

3 Replies

Solution

Huge Letters•9mo ago

btw even tho this is unwarranted advice - is winrate column even needed? you could always compute it pretty easily on demand from the other two, no?

jinxOP•9mo ago

i suppose it's not technically needed per say. i thought it'd be easier to work with, if there was a single source of truth for it, instead of having to recalculate it whenever it's needed and also, it makes some other parts much easier to handle. such as doing stuff with tanstack table and using the data fetched from the db directly without having to tranform it beforehand

Gaming

Programming

trying to create a trigger on supabase to calculate a winrate based on two other columns