2 create table site_aggregates (
4 site_id int references site on update cascade on delete cascade not null,
5 users bigint not null default 1,
6 posts bigint not null default 0,
7 comments bigint not null default 0,
8 communities bigint not null default 0
11 insert into site_aggregates (site_id, users, posts, comments, communities)
13 ( select coalesce(count(*), 0) from user_ where local = true) as users,
14 ( select coalesce(count(*), 0) from post where local = true) as posts,
15 ( select coalesce(count(*), 0) from comment where local = true) as comments,
16 ( select coalesce(count(*), 0) from community where local = true) as communities
20 create function site_aggregates_site()
21 returns trigger language plpgsql
24 IF (TG_OP = 'INSERT') THEN
25 insert into site_aggregates (site_id) values (NEW.id);
26 ELSIF (TG_OP = 'DELETE') THEN
27 delete from site_aggregates where site_id = OLD.id;
32 create trigger site_aggregates_site
33 after insert or delete on site
35 execute procedure site_aggregates_site();
37 -- Add site aggregate triggers
39 create function site_aggregates_user_insert()
40 returns trigger language plpgsql
43 update site_aggregates
44 set users = users + 1;
48 create function site_aggregates_user_delete()
49 returns trigger language plpgsql
52 -- Join to site since the creator might not be there anymore
53 update site_aggregates sa
56 where sa.site_id = s.id;
60 create trigger site_aggregates_user_insert
63 when (NEW.local = true)
64 execute procedure site_aggregates_user_insert();
66 create trigger site_aggregates_user_delete
69 when (OLD.local = true)
70 execute procedure site_aggregates_user_delete();
73 create function site_aggregates_post_insert()
74 returns trigger language plpgsql
77 update site_aggregates
78 set posts = posts + 1;
82 create function site_aggregates_post_delete()
83 returns trigger language plpgsql
86 update site_aggregates sa
89 where sa.site_id = s.id;
93 create trigger site_aggregates_post_insert
96 when (NEW.local = true)
97 execute procedure site_aggregates_post_insert();
99 create trigger site_aggregates_post_delete
102 when (OLD.local = true)
103 execute procedure site_aggregates_post_delete();
106 create function site_aggregates_comment_insert()
107 returns trigger language plpgsql
110 update site_aggregates
111 set comments = comments + 1;
115 create function site_aggregates_comment_delete()
116 returns trigger language plpgsql
119 update site_aggregates sa
120 set comments = comments - 1
122 where sa.site_id = s.id;
126 create trigger site_aggregates_comment_insert
127 after insert on comment
129 when (NEW.local = true)
130 execute procedure site_aggregates_comment_insert();
132 create trigger site_aggregates_comment_delete
133 after delete on comment
135 when (OLD.local = true)
136 execute procedure site_aggregates_comment_delete();
139 create function site_aggregates_community_insert()
140 returns trigger language plpgsql
143 update site_aggregates
144 set communities = communities + 1;
148 create function site_aggregates_community_delete()
149 returns trigger language plpgsql
152 update site_aggregates sa
153 set communities = communities - 1
155 where sa.site_id = s.id;
159 create trigger site_aggregates_community_insert
160 after insert on community
162 when (NEW.local = true)
163 execute procedure site_aggregates_community_insert();
165 create trigger site_aggregates_community_delete
166 after delete on community
168 when (OLD.local = true)
169 execute procedure site_aggregates_community_delete();