Counter Cache in SQL

counter_cache.sql

CREATE OR REPLACE FUNCTION comments_count_update()
RETURNS TRIGGER AS $$
BEGIN
    -- Increase count on insert
    IF (TG_OP = 'INSERT') THEN
        UPDATE posts SET comments_count = comments_count + 1
        WHERE posts.id = NEW.post_id;
        RETURN NEW;
    -- Decrease count on delete
    ELSIF (TG_OP = 'DELETE') THEN
        UPDATE posts SET comments_count = comments_count - 1
        WHERE posts.id = OLD.post_id;
        RETURN OLD;
    -- Handle case where comment might change its post
    ELSIF (TG_OP = 'UPDATE' AND OLD.post_id != NEW.post_id) THEN
        UPDATE posts SET comments_count = comments_count - 1
        WHERE posts.id = OLD.post_id;
        UPDATE posts SET comments_count = comments_count + 1
        WHERE posts.id = NEW.post_id;
        RETURN NEW;
    END IF;
END;

-- register the driggers
CREATE TRIGGER comments_insert AFTER INSERT ON comments
FOR EACH ROW EXECUTE FUNCTION comments_count_update();

CREATE TRIGGER comments_delete AFTER DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION comments_count_update();

CREATE TRIGGER comments_update AFTER UPDATE ON comments
FOR EACH ROW EXECUTE FUNCTION comments_count_update();