2 create table site_aggregates (
6 comments bigint not null,
7 communities bigint not null
10 insert into site_aggregates (users, posts, comments, communities)
11 select ( select coalesce(count(*), 0) from user_) as users,
12 ( select coalesce(count(*), 0) from post) as posts,
13 ( select coalesce(count(*), 0) from comment) as comments,
14 ( select coalesce(count(*), 0) from community) as communities;
16 -- Add site aggregate triggers
18 create function site_aggregates_user()
19 returns trigger language plpgsql
22 IF (TG_OP = 'INSERT') THEN
23 update site_aggregates
24 set users = users + 1;
25 ELSIF (TG_OP = 'DELETE') THEN
26 update site_aggregates
27 set users = users - 1;
32 create trigger site_aggregates_user
33 after insert or delete on user_
34 execute procedure site_aggregates_user();
37 create function site_aggregates_post()
38 returns trigger language plpgsql
41 IF (TG_OP = 'INSERT') THEN
42 update site_aggregates
43 set posts = posts + 1;
44 ELSIF (TG_OP = 'DELETE') THEN
45 update site_aggregates
46 set posts = posts - 1;
51 create trigger site_aggregates_post
52 after insert or delete on post
54 execute procedure site_aggregates_post();
57 create function site_aggregates_comment()
58 returns trigger language plpgsql
61 IF (TG_OP = 'INSERT') THEN
62 update site_aggregates
63 set comments = comments + 1;
64 ELSIF (TG_OP = 'DELETE') THEN
65 update site_aggregates
66 set comments = comments - 1;
71 create trigger site_aggregates_comment
72 after insert or delete on comment
74 execute procedure site_aggregates_comment();
77 create function site_aggregates_community()
78 returns trigger language plpgsql
81 IF (TG_OP = 'INSERT') THEN
82 update site_aggregates
83 set communities = communities + 1;
84 ELSIF (TG_OP = 'DELETE') THEN
85 update site_aggregates
86 set communities = communities - 1;
91 create trigger site_aggregates_community
92 after insert or delete on community
94 execute procedure site_aggregates_community();