PostgreSQL trigger update average and count when new value in column is added

Question

This is my table:

CREATE TABLE mark (
EID serial,
PID integer,
SID integer,
score integer DEFAULT 5 NOT NULL,
CONSTRAINT PK_EID PRIMARY KEY(EID),
CONSTRAINT "FK_personne_ID"
FOREIGN KEY (PID)
REFERENCES personne (PID)
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT "FK_serie_ID"
FOREIGN KEY (SID)
REFERENCES serie (SID)
ON UPDATE RESTRICT ON DELETE RESTRICT
);

My trigger : when a new row/value is inserted in the column "score", updates the average score and the total count of scores: I'm not sure if I should implement a function ahead or start with the trigger directly :

CREATE OR REPLACE FUNCTION FunctionUpdateScore(float) RETURNS integer AS 
'BEGIN
SELECT COUNT(score) AS nb_score, AVG(score) AS ag_score 
FROM mark;
END;'
LANGUAGE 'plpgsql';

--trigger
CREATE or REPLACE TRIGGER TriggerUpdateScore
AFTER INSERT 
ON mark
FOR EACH ROW
EXECUTE PROCEDURE FunctionUpdateScore();
UPDATE nb_score
SET nb_score= nb_score+ 1
END;

Answer 1

1. Properly escape your trigger body. Use dollar quoting
2. Remove the float argument from your function
3. Actually perform the update in your function
4. Update the correct table (you are updating the nb_score table in your example which does not have a trigger on it, therefore it will not fire off the trigger)
5. Specify a relationship between mark and nb_score. As of now the trigger function below updates all nb_score rows, not just the one that is related to the inserted row in mark.;
6. Your function return type must be trigger

CREATE OR REPLACE FUNCTION FunctionUpdateScore() RETURNS trigger AS $$
BEGIN
 UPDATE nb_score
    SET 
        nb_score=COUNT(score),
        ag_score=AVG(score)
    FROM mark;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER TriggerUpdateScore
AFTER INSERT ON mark FOR EACH ROW EXECUTE PROCEDURE FunctionUpdateScore();