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 0,
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 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 update site_aggregates
48 set users = users - 1;
53 create trigger site_aggregates_user
54 after insert or delete on user_
56 execute procedure site_aggregates_user();
59 create function site_aggregates_post()
60 returns trigger language plpgsql
63 IF (TG_OP = 'INSERT') THEN
64 update site_aggregates
65 set posts = posts + 1;
66 ELSIF (TG_OP = 'DELETE') THEN
67 update site_aggregates
68 set posts = posts - 1;
73 create trigger site_aggregates_post
74 after insert or delete on post
76 execute procedure site_aggregates_post();
79 create function site_aggregates_comment()
80 returns trigger language plpgsql
83 IF (TG_OP = 'INSERT') THEN
84 update site_aggregates
85 set comments = comments + 1;
86 ELSIF (TG_OP = 'DELETE') THEN
87 update site_aggregates
88 set comments = comments - 1;
93 create trigger site_aggregates_comment
94 after insert or delete on comment
96 execute procedure site_aggregates_comment();
99 create function site_aggregates_community()
100 returns trigger language plpgsql
103 IF (TG_OP = 'INSERT') THEN
104 update site_aggregates
105 set communities = communities + 1;
106 ELSIF (TG_OP = 'DELETE') THEN
107 update site_aggregates
108 set communities = communities - 1;
113 create trigger site_aggregates_community
114 after insert or delete on community
116 execute procedure site_aggregates_community();