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_) as users,
14 ( select coalesce(count(*), 0) from post) as posts,
15 ( select coalesce(count(*), 0) from comment) as comments,
16 ( select coalesce(count(*), 0) from community) 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 or replace function site_aggregates_user()
40 returns trigger language plpgsql
43 IF (TG_OP = 'INSERT') THEN
44 update site_aggregates
45 set users = users + 1;
46 ELSIF (TG_OP = 'DELETE') THEN
47 -- Join to site since the creator might not be there anymore
48 update site_aggregates sa
51 where sa.site_id = s.id;
56 create trigger site_aggregates_user
57 after insert or delete on user_
59 execute procedure site_aggregates_user();
62 create function site_aggregates_post()
63 returns trigger language plpgsql
66 IF (TG_OP = 'INSERT') THEN
67 update site_aggregates
68 set posts = posts + 1;
69 ELSIF (TG_OP = 'DELETE') THEN
70 update site_aggregates sa
73 where sa.site_id = s.id;
78 create trigger site_aggregates_post
79 after insert or delete on post
81 execute procedure site_aggregates_post();
84 create function site_aggregates_comment()
85 returns trigger language plpgsql
88 IF (TG_OP = 'INSERT') THEN
89 update site_aggregates
90 set comments = comments + 1;
91 ELSIF (TG_OP = 'DELETE') THEN
92 update site_aggregates sa
93 set comments = comments - 1
95 where sa.site_id = s.id;
100 create trigger site_aggregates_comment
101 after insert or delete on comment
103 execute procedure site_aggregates_comment();
106 create function site_aggregates_community()
107 returns trigger language plpgsql
110 IF (TG_OP = 'INSERT') THEN
111 update site_aggregates
112 set communities = communities + 1;
113 ELSIF (TG_OP = 'DELETE') THEN
114 update site_aggregates sa
115 set communities = communities - 1
117 where sa.site_id = s.id;
122 create trigger site_aggregates_community
123 after insert or delete on community
125 execute procedure site_aggregates_community();