Create Trigger in Postgres asking for avg()

Question

I'm trying to create a Trigger that adds the average of one column from table A into table B after each insert into table A.

Table A (id (integer), rating (integer))
Table B (id (serial), average (integer))

My Trigger is:

CREATE TRIGGER rat_trig 
  AFTER INSERT ON a 
  FOR EACH ROW EXECUTE PROCEDURE upd_rat_func();

My Trigger Function looks like this:

CREATE OR REPLACE FUNCTION upd_rat_func() RETURNS TRIGGER as $rating_update$
BEGIN
INSERT INTO b VALUES (DEFAULT, AVG(rating) FROM a);
RETURN NEW;
END;
$rating_update$ language plpgsql;

Postgres tells me there is an error in my function somewhere near or at 'from', yet I cannot find a way to make it work (The Trigger is not yet created, as I cannot create the function). Any help (or also any advice of where I can find more about the structure of trigger functions as the postgres documentation has been less than helpful) are very welcome.

Laurenz Albe · Accepted Answer · 2021-05-31 13:51:19Z

0

The FROM has to belong to a SELECT (or a DELETE).

To use a subquery, use the complete query and put it into parentheses:

INSERT INTO b VALUES (DEFAULT, (SELECT AVG(rating) FROM a));

But that trigger does not make much sense to me. You want a new row with a new average for each INSERT? What about the other rows in b?

Perhaps you are looking for a materialized view.

answered May 31, 2021 at 13:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

Bee Over a year ago

Thank you, this works. And yes, this was just the part of the code that wasn't working, the trigger is a little different actually.

Slava Rozhnev · Accepted Answer · 2021-05-31 14:13:16Z

0

Here is PostgreSQL doc:

https://www.postgresql.org/docs/12/plpgsql-trigger.html

try changing INSERT statement for smth like this:

INSERT INTO b (average) SELECT AVG(rating) FROM a;

make sure it works before adding it to trigger function

edited May 31, 2021 at 14:13

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

answered May 31, 2021 at 13:51

Владимир Федченко

11 bronze badge

Collectives™ on Stack Overflow

Create Trigger in Postgres asking for avg()

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related