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_
35 execute procedure site_aggregates_user();
38 create function site_aggregates_post()
39 returns trigger language plpgsql
42 IF (TG_OP = 'INSERT') THEN
43 update site_aggregates
44 set posts = posts + 1;
45 ELSIF (TG_OP = 'DELETE') THEN
46 update site_aggregates
47 set posts = posts - 1;
52 create trigger site_aggregates_post
53 after insert or delete on post
55 execute procedure site_aggregates_post();
58 create function site_aggregates_comment()
59 returns trigger language plpgsql
62 IF (TG_OP = 'INSERT') THEN
63 update site_aggregates
64 set comments = comments + 1;
65 ELSIF (TG_OP = 'DELETE') THEN
66 update site_aggregates
67 set comments = comments - 1;
72 create trigger site_aggregates_comment
73 after insert or delete on comment
75 execute procedure site_aggregates_comment();
78 create function site_aggregates_community()
79 returns trigger language plpgsql
82 IF (TG_OP = 'INSERT') THEN
83 update site_aggregates
84 set communities = communities + 1;
85 ELSIF (TG_OP = 'DELETE') THEN
86 update site_aggregates
87 set communities = communities - 1;
92 create trigger site_aggregates_community
93 after insert or delete on community
95 execute procedure site_aggregates_community();