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 0,
   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 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     update site_aggregates
  48     set users = users - 1;
  49   END IF;
  50   return null;
  51 end $$;
  52
  53 create trigger site_aggregates_user
  54 after insert or delete on user_
  55 for each row
  56 execute procedure site_aggregates_user();
  57
  58 -- post
  59 create function site_aggregates_post()
  60 returns trigger language plpgsql
  61 as $$
  62 begin
  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;
  69   END IF;
  70   return null;
  71 end $$;
  72
  73 create trigger site_aggregates_post
  74 after insert or delete on post
  75 for each row
  76 execute procedure site_aggregates_post();
  77
  78 -- comment
  79 create function site_aggregates_comment()
  80 returns trigger language plpgsql
  81 as $$
  82 begin
  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;
  89   END IF;
  90   return null;
  91 end $$;
  92
  93 create trigger site_aggregates_comment
  94 after insert or delete on comment
  95 for each row
  96 execute procedure site_aggregates_comment();
  97
  98 -- community
  99 create function site_aggregates_community()
 100 returns trigger language plpgsql
 101 as $$
 102 begin
 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;
 109   END IF;
 110   return null;
 111 end $$;
 112
 113 create trigger site_aggregates_community
 114 after insert or delete on community
 115 for each row
 116 execute procedure site_aggregates_community();
 117