leaflet.pub
a tool for shared writing and social publishing
awarm.space
1ALTER TABLE documents ADD COLUMN recommend_count integer NOT NULL DEFAULT 0;
2
3UPDATE documents d
4SET recommend_count = (
5 SELECT COUNT(*) FROM recommends_on_documents r WHERE r.document = d.uri
6);
7
8CREATE OR REPLACE FUNCTION update_recommend_count() RETURNS trigger AS $$
9BEGIN
10 IF TG_OP = 'INSERT' THEN
11 UPDATE documents SET recommend_count = recommend_count + 1
12 WHERE uri = NEW.document;
13 ELSIF TG_OP = 'DELETE' THEN
14 UPDATE documents SET recommend_count = recommend_count - 1
15 WHERE uri = OLD.document;
16 END IF;
17 RETURN NULL;
18END;
19$$ LANGUAGE plpgsql;
20
21CREATE TRIGGER trg_recommend_count
22AFTER INSERT OR DELETE ON recommends_on_documents
23FOR EACH ROW EXECUTE FUNCTION update_recommend_count();