2 ALTER TABLE forum_threads ADD posts INTEGER NOT NULL DEFAULT 0;
3 ALTER TABLE forum_threads ADD mtime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now();
4 ALTER TABLE forum_threads ADD ctime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now();
7 CREATE OR REPLACE FUNCTION update_forum_thread_posts() RETURNS trigger
11 IF TG_OP = 'INSERT' THEN
12 UPDATE forum_threads SET posts = posts + 1, mtime = GREATEST(NEW.time,mtime)
13 WHERE ftid = NEW.ftid;
14 ELSIF TG_OP = 'DELETE' THEN
15 UPDATE forum_threads SET posts = posts - 1 WHERE ftid = OLD.ftid;
16 ELSIF TG_OP = 'UPDATE' AND NEW.ftid <> OLD.ftid THEN
17 UPDATE forum_threads SET posts = posts - 1 WHERE ftid = OLD.ftid;
18 UPDATE forum_threads SET posts = posts + 1, mtime = GREATEST(NEW.time,mtime)
19 WHERE ftid = NEW.ftid;
28 CREATE TRIGGER update_forum_thread_posts
29 AFTER INSERT OR UPDATE OR DELETE ON forum_posts
31 EXECUTE PROCEDURE update_forum_thread_posts();
33 UPDATE forum_threads ft SET posts = p.posts, mtime = p.time, ctime = p.ctime
34 FROM (SELECT ftid, count(fpid) AS posts, max(time) AS time, min(time) AS ctime
35 FROM forum_posts group by ftid) p
36 WHERE p.ftid = ft.ftid;