create table site_aggregates (
id serial primary key,
site_id int references site on update cascade on delete cascade not null,
- users bigint not null default 0,
+ users bigint not null default 1,
posts bigint not null default 0,
comments bigint not null default 0,
communities bigint not null default 0
-- Add site aggregate triggers
-- user
-create function site_aggregates_user()
+create or replace function site_aggregates_user()
returns trigger language plpgsql
as $$
begin
update site_aggregates
set users = users + 1;
ELSIF (TG_OP = 'DELETE') THEN
- update site_aggregates
- set users = users - 1;
+ -- Join to site since the creator might not be there anymore
+ update site_aggregates sa
+ set users = users - 1
+ from site s
+ where sa.site_id = s.id;
END IF;
return null;
end $$;
update site_aggregates
set posts = posts + 1;
ELSIF (TG_OP = 'DELETE') THEN
- update site_aggregates
- set posts = posts - 1;
+ update site_aggregates sa
+ set posts = posts - 1
+ from site s
+ where sa.site_id = s.id;
END IF;
return null;
end $$;
update site_aggregates
set comments = comments + 1;
ELSIF (TG_OP = 'DELETE') THEN
- update site_aggregates
- set comments = comments - 1;
+ update site_aggregates sa
+ set comments = comments - 1
+ from site s
+ where sa.site_id = s.id;
END IF;
return null;
end $$;
update site_aggregates
set communities = communities + 1;
ELSIF (TG_OP = 'DELETE') THEN
- update site_aggregates
- set communities = communities - 1;
+ update site_aggregates sa
+ set communities = communities - 1
+ from site s
+ where sa.site_id = s.id;
END IF;
return null;
end $$;