Postgres Function/Trigger Avg

Question

I am trying to create a function that calculates the moving average for an item. What I have is a table of "item" and then each user can "vote" which produces one vote, with a score, user_id and item_id.

I want to calculate an AVG, which is stored in item_id after every insert and is calculated based on number of vote's (selected by vote.item_id) and averages the score using vote.score.

this is the idea of the query.. this works, but stores the moving average int he last column of vote and is not usable, but I am close!!

select vote.score as single_vote_score, vote.user_id, vote.item_id, items.score as item_score, 
    AVG(vote.score) 
        OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
FROM vote 
    LEFT JOIN items on vote.item_id = items.id 
WHERE vote.item_id = 10;

This is the function

CREATE FUNCTION calculate_item_score() RETURNS TRIGGER AS $calculates_items_score_by_average$ 
    BEGIN UPDATE "item" 
        SET "score" = AVG("vote"."score") 
        FROM "vote" 
        WHERE "item"."id"="vote"."item_id"; 
        RETURN NEW; 
    END; 
$calculates_items_score_by_average$ 
LANGUAGE plpgsql;

This trigger should run every time a vote is inserted!

CREATE TRIGGER "update_item_score" 
AFTER INSERT ON "vote" 
FOR EACH ROW EXECUTE 
PROCEDURE calculate_item_score();

-- this errors out
INSERT INTO vote (user_id, photo_id, score) VALUES ('129','10','50.0');

This is my error

ERROR:  aggregate functions are not allowed in UPDATE
LINE 1: UPDATE "photo" SET "score" = AVG("vote"."score") FROM "vote"...
                                 ^
QUERY:  UPDATE "item" SET "score" = AVG("vote"."score") FROM "vote" WHERE 
"item"."id"="vote"."item_id"
CONTEXT:  PL/pgSQL function calculate_item_score() line 1 at SQL statement

Answer 1

BEGIN UPDATE "item" 
    SET "score" = (select AVG("vote"."score") FROM "vote")     

Use it as follows:

create or replace function test (id int) returns void as 
$$ 
begin 
update employees 
set commission_pct=(select min(commission_pct) from employees) 
where employee_id= $1; 
end 
$$ LANGUAGE plpgsql VOLATILE; 

select test(198); --call function 

The process of aggregate functions is calculated after getting data set from data in table, we cannot use any calculating without selecting data from table first. In the above function is update column score* to AVG(score) but there is not data-set to calculate the AVG(score).

Answer 2

ERROR: aggregate functions are not allowed in UPDATE

How much clearer can you get than that? First, a few things: this is a bad idea. You're essentially forcing at the very least an index scan, and at the very most a full table seq scan. That's a lot for an update.

Some other ideas:

• Window functions.
• [MATERIALIZED] VIEWS.

this is the idea of the query.. this works, but stores the moving average int he last column of vote and is not usable, but I am close!!

I'm not sure what "the last column of vote" means, but if that's you're only problem, then simply move it to a different position in the SELECT list?

SELECT
  AVG(vote.score) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  vote.score as single_vote_score,
  vote.user_id,
  vote.item_id,
  items.score AS item_score
FROM vote 
LEFT JOIN items
  ON vote.item_id = items.id 
WHERE vote.item_id = 10;

I'm not sure why the position of it matters though?