migrations/2020-12-02-152437_create_site_aggregates/up.sql

   1 -- Add site aggregates
   2 create table site_aggregates (
   3   id serial primary key,
   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
   9 );
  10
  11 insert into site_aggregates (site_id, users, posts, comments, communities)
  12   select id as site_id,
  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
  17   from site;
  18
  19 -- initial site add
  20 create function site_aggregates_site()
  21 returns trigger language plpgsql
  22 as $$
  23 begin
  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;
  28   END IF;
  29   return null;
  30 end $$;
  31
  32 create trigger site_aggregates_site
  33 after insert or delete on site
  34 for each row
  35 execute procedure site_aggregates_site();
  36
  37 -- Add site aggregate triggers
  38 -- user
  39 create or replace function site_aggregates_user()
  40 returns trigger language plpgsql
  41 as $$
  42 begin
  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
  49     set users = users - 1
  50     from site s
  51     where sa.site_id = s.id;
  52   END IF;
  53   return null;
  54 end $$;
  55
  56 create trigger site_aggregates_user
  57 after insert or delete on user_
  58 for each row
  59 execute procedure site_aggregates_user();
  60
  61 -- post
  62 create function site_aggregates_post()
  63 returns trigger language plpgsql
  64 as $$
  65 begin
  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
  71     set posts = posts - 1
  72     from site s
  73     where sa.site_id = s.id;
  74   END IF;
  75   return null;
  76 end $$;
  77
  78 create trigger site_aggregates_post
  79 after insert or delete on post
  80 for each row
  81 execute procedure site_aggregates_post();
  82
  83 -- comment
  84 create function site_aggregates_comment()
  85 returns trigger language plpgsql
  86 as $$
  87 begin
  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
  94     from site s
  95     where sa.site_id = s.id;
  96   END IF;
  97   return null;
  98 end $$;
  99
 100 create trigger site_aggregates_comment
 101 after insert or delete on comment
 102 for each row
 103 execute procedure site_aggregates_comment();
 104
 105 -- community
 106 create function site_aggregates_community()
 107 returns trigger language plpgsql
 108 as $$
 109 begin
 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
 116     from site s
 117     where sa.site_id = s.id;
 118   END IF;
 119   return null;
 120 end $$;
 121
 122 create trigger site_aggregates_community
 123 after insert or delete on community
 124 for each row
 125 execute procedure site_aggregates_community();
 126