What is the proper way to update a value after update or insert?

Hello everyone! So I have 2 tables. Table 1 is Profiles and table 2 is Videos. Videos table has a foreign key to the profiles table based on the user_id. On the profiles table I have a column which is called avg_views which basically should be the avarage views of that profile based on the views column on the videos table.

So I have a trigger

-- Create a trigger function
CREATE OR REPLACE FUNCTION profile_update_avg_views_30d()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE profiles
  SET avg_views_30d = (
    SELECT AVG(views)
    FROM videos
    WHERE profile = NEW.user_id
      AND posting_date >= CURRENT_DATE - INTERVAL '30 days'
  )
  WHERE user_id = NEW.user_id; 

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger that fires after every update or insert on the videos table
CREATE TRIGGER profile_update_avg_views_30d_trigger
AFTER INSERT OR UPDATE ON profiles
FOR EACH ROW
EXECUTE FUNCTION profile_update_avg_views_30d();

-- Create a trigger function
CREATE OR REPLACE FUNCTION profile_update_avg_views_30d()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE profiles
  SET avg_views_30d = (
    SELECT AVG(views)
    FROM videos
    WHERE profile = NEW.user_id
      AND posting_date >= CURRENT_DATE - INTERVAL '30 days'
  )
  WHERE user_id = NEW.user_id; 

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger that fires after every update or insert on the videos table
CREATE TRIGGER profile_update_avg_views_30d_trigger
AFTER INSERT OR UPDATE ON profiles
FOR EACH ROW
EXECUTE FUNCTION profile_update_avg_views_30d();

But it seems to cause statement_timeout errors.

I have many more queries like the one above that update different columns. My question is: is this properly written? How can I do this in a better and optimal way?

Thanks in advance!

-- Create a trigger function
CREATE OR REPLACE FUNCTION profile_update_avg_views_30d()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE profiles
  SET avg_views_30d = (
    SELECT AVG(views)
    FROM videos
    WHERE profile = NEW.user_id
      AND posting_date >= CURRENT_DATE - INTERVAL '30 days'
  )
  WHERE user_id = NEW.user_id; 

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger that fires after every update or insert on the videos table
CREATE TRIGGER profile_update_avg_views_30d_trigger
AFTER INSERT OR UPDATE ON profiles
FOR EACH ROW
EXECUTE FUNCTION profile_update_avg_views_30d();

-- Create a trigger function
CREATE OR REPLACE FUNCTION profile_update_avg_views_30d()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE profiles
  SET avg_views_30d = (
    SELECT AVG(views)
    FROM videos
    WHERE profile = NEW.user_id
      AND posting_date >= CURRENT_DATE - INTERVAL '30 days'
  )
  WHERE user_id = NEW.user_id; 

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger that fires after every update or insert on the videos table
CREATE TRIGGER profile_update_avg_views_30d_trigger
AFTER INSERT OR UPDATE ON profiles
FOR EACH ROW
EXECUTE FUNCTION profile_update_avg_views_30d();

What is the proper way to update a value after update or insert?

Similar Threads

What is the proper way to update a value after update or insert?

Similar Threads

Similar Threads

Similar Threads