PostgreSQL Create Trigger which runs a function on every insert or update of a table

Question

I have defined two tables, scores and analyzed_avg_score, in my postgres database. I also have a function which i declaired like that:

CREATE FUNCTION updateAvgScore() RETURNS void AS $$
    INSERT into analyzed_avg_score
        (SELECT 
            user,
            avg(score_value)
        FROM
            scores
        group by user) on conflict do nothing;
$$ LANGUAGE SQL;

Now, I want to have a trigger or something similar that runs this function every time I insert or update something in score. I don't have a lot of experience with SQL, yet. So, does anyone have an idea how the trigger should look like?

While you can do this with a trigger, it would be much simpler to just create a view that shows the average per user. If you do end up using a trigger, don't forget about running it on deletes as well and modifying your current function to update analyzed_avg_score when the user already exists instead of doing nothing. — Jeremy
– Jeremy, Commented Nov 15, 2019 at 19:42
didn't thought about creating a view. That's definitly the better way, thanks! — karrax
– karrax, Commented Nov 15, 2019 at 20:02

Selaka Nanayakkara · Accepted Answer · 2019-11-16 11:49:43Z

2

CREATE TRIGGER SCORE_INSERT AFTER INSERT ON SCORE
FOR EACH ROW EXECUTE PROCEDURE updateAvgScore();


/*Have it return a trigger like this */

CREATE OR REPLACE FUNCTION updateAvgScore() RETURNS TRIGGER AS $example_table$
   BEGIN
      /*YOUR lOGIC HERE*/
   END;
$example_table$ LANGUAGE plpgsql;

edited Nov 16, 2019 at 11:49

answered Nov 15, 2019 at 19:28

Selaka Nanayakkara

6,9963 gold badges27 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

karrax Over a year ago

I get the following error when I execute it: ERROR: function updateavgscore must return type trigger

Selaka Nanayakkara Over a year ago

Updated please check now

Collectives™ on Stack Overflow

PostgreSQL Create Trigger which runs a function on every insert or update of a table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related