Supabase function doesn't do any calculations

Question

I have a function:

begin
    if pg_trigger_depth() <> 1 then
        return new;
    end if;
   new.rating = ROUND((new.total_score / new.total_reviews), 1);
   return new;
end;

This function must update column rating in table route every time I make an update on route table. Now the query if firing, total_score and total_reviews is getting updates, but column rating stays the same. I mean, there is no calculation on it and it's never change. No matter what's values I have in needed columns.

table route definition:

create table
  public.route (
    id bigint generated by default as identity not null,
    name text null,
    country text null,
    city text null,
    description text null,
    imageName text null,
    created_at timestamp with time zone null default now(),
    total_reviews numeric null,
    total_score numeric null,
    duration numeric not null default '30'::numeric,
    rating double precision null,
    isAdult boolean not null default false,
    constraint route_pkey primary key (id)
  ) tablespace pg_default;

create trigger update_route_rating_trigger
after insert
or
update on route for each row
execute function update_route_rating ();

RLS Policies on route:

---

Policy name: allow_update_on_route

Target roles: public

USING expression: true

WITH CHECK expression: true

---

Policy name: Authorized users can select from route table

Target roles: public

USING expression: (auth.role() = 'authenticated'::text)

---

Answer 1

As @Belayer said, my solution won't work cuz it has after row trigger and an after trigger cannot change data values, so that's why in this solution, I should have used before row trigger.

Also, the best working solution (IMHO) is a generated-columns, as @dshukertjr said.

Here is a link to generated columns doc for help: postgresql.org/generated-columns