Here's the SQL query for the function which get the post_id whenever there is a new entry in the table voters and based on that it count all the posts having same ID and set that value in the post table in a column.
Function
CREATE OR REPLACE FUNCTION update_vote_count()
RETURNS trigger
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
UPDATE post SET votes = (SELECT COUNT(*) FROM voters WHERE post_id = NEW.post_id) WHERE post_id = NEW.post_id;
RETURN NEW;
END;
$BODY$
Trigger
CREATE TRIGGER voters_count
AFTER UPDATE
ON voters
FOR EACH ROW
EXECUTE PROCEDURE update_vote_count();
The problem is that, this function is not triggering and I don't even know how to debug these database functions/triggers.
Top comments (2)
Dam! That's a really good suggestion, why didn't I thought of that even though we use
console.log()statements inside the codebase a lot. 😅Also I will also look into logging and LISTEN/NOTIFY.