Create trigger to automatically update column with subquery

In my application, I have a couple of tables, lessons and votes. Here’s what they look like:

Lessons

+-------------+---------+----------------------+
| Column      | Type    | Modifiers            |
|-------------+---------+----------------------|
| id          | uuid    |  not null            |
| votes_total | integer |  not null default 0  |
+-------------+---------+----------------------+

Votes

+-------------+---------+-------------+
| Column      | Type    | Modifiers   |
|-------------+---------+-------------|
| positive    | boolean |  not null   |
| user_id     | uuid    |  not null   |
| lesson_id   | uuid    |  not null   |
+-------------+---------+-------------+

Whenever a row in votes is inserted, updated or deleted, I’d like to update the votes_total column of the related lesson using a subquery. Here’s what I’ve tried:

CREATE PROCEDURE update_lesson_votes_total()
LANGUAGE SQL
AS $$
UPDATE lessons
SET votes_total = (
  SELECT SUM(CASE WHEN positive THEN 1 ELSE -1 END)
  FROM votes
  WHERE lesson_id = NEW.lesson_id
)
WHERE id = NEW.lesson_id;
$$;

CREATE TRIGGER votes_change
AFTER INSERT OR UPDATE OR DELETE ON votes
FOR EACH ROW
EXECUTE PROCEDURE update_lesson_votes_total();

However, when I try to run this in a migration, I get the following error:

(Postgrex.Error) ERROR 42601 (syntax_error) cannot insert multiple commands into a prepared statement

Answer

Hi your function must return a trigger. You can’t use new in a trigger on delete. You have to create at least 2 triggers calling the same function and use TG_OP to know if the function is triggered by insert, update or delete.

Then in a case or if statement you can use new or old to get the id’s value.

CREATE FUNCTION update_lesson_votes_total()
returns trigger
LANGUAGE plpgsql
AS $$
begin
UPDATE lessons
SET votes_total = (
  SELECT SUM(CASE WHEN positive THEN 1 ELSE -1 END)
  FROM votes
  WHERE lesson_id = NEW.lesson_id
)
WHERE id = NEW.lesson_id;

return null;
end;
$$;

CREATE TRIGGER votes_change_u_i
AFTER INSERT OR UPDATE ON votes
FOR EACH ROW
EXECUTE PROCEDURE update_lesson_votes_total();

CREATE TRIGGER votes_change_d
AFTER DELETE ON votes
FOR EACH ROW
EXECUTE PROCEDURE update_lesson_votes_total();