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   users bigint not null,
   5   posts bigint not null,
   6   comments bigint not null,
   7   communities bigint not null
   8 );
   9
  10 insert into site_aggregates (users, posts, comments, communities)
  11   select ( select coalesce(count(*), 0) from user_) as users,
  12   ( select coalesce(count(*), 0) from post) as posts,
  13   ( select coalesce(count(*), 0) from comment) as comments,
  14   ( select coalesce(count(*), 0) from community) as communities;
  15
  16 -- Add site aggregate triggers
  17 -- user
  18 create function site_aggregates_user()
  19 returns trigger language plpgsql
  20 as $$
  21 begin
  22   IF (TG_OP = 'INSERT') THEN
  23     update site_aggregates
  24     set users = users + 1;
  25   ELSIF (TG_OP = 'DELETE') THEN
  26     update site_aggregates
  27     set users = users - 1;
  28   END IF;
  29   return null;
  30 end $$;
  31
  32 create trigger site_aggregates_user
  33 after insert or delete on user_
  34 execute procedure site_aggregates_user();
  35
  36 -- post
  37 create function site_aggregates_post()
  38 returns trigger language plpgsql
  39 as $$
  40 begin
  41   IF (TG_OP = 'INSERT') THEN
  42     update site_aggregates
  43     set posts = posts + 1;
  44   ELSIF (TG_OP = 'DELETE') THEN
  45     update site_aggregates
  46     set posts = posts - 1;
  47   END IF;
  48   return null;
  49 end $$;
  50
  51 create trigger site_aggregates_post
  52 after insert or delete on post
  53 for each row
  54 execute procedure site_aggregates_post();
  55
  56 -- comment
  57 create function site_aggregates_comment()
  58 returns trigger language plpgsql
  59 as $$
  60 begin
  61   IF (TG_OP = 'INSERT') THEN
  62     update site_aggregates
  63     set comments = comments + 1;
  64   ELSIF (TG_OP = 'DELETE') THEN
  65     update site_aggregates
  66     set comments = comments - 1;
  67   END IF;
  68   return null;
  69 end $$;
  70
  71 create trigger site_aggregates_comment
  72 after insert or delete on comment
  73 for each row
  74 execute procedure site_aggregates_comment();
  75
  76 -- community
  77 create function site_aggregates_community()
  78 returns trigger language plpgsql
  79 as $$
  80 begin
  81   IF (TG_OP = 'INSERT') THEN
  82     update site_aggregates
  83     set communities = communities + 1;
  84   ELSIF (TG_OP = 'DELETE') THEN
  85     update site_aggregates
  86     set communities = communities - 1;
  87   END IF;
  88   return null;
  89 end $$;
  90
  91 create trigger site_aggregates_community
  92 after insert or delete on community
  93 for each row
  94 execute procedure site_aggregates_community();
  95