Postgres Function/Trigger Avg 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
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).