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 for each row
  35 execute procedure site_aggregates_user();
  36
  37 -- post
  38 create function site_aggregates_post()
  39 returns trigger language plpgsql
  40 as $$
  41 begin
  42   IF (TG_OP = 'INSERT') THEN
  43     update site_aggregates
  44     set posts = posts + 1;
  45   ELSIF (TG_OP = 'DELETE') THEN
  46     update site_aggregates
  47     set posts = posts - 1;
  48   END IF;
  49   return null;
  50 end $$;
  51
  52 create trigger site_aggregates_post
  53 after insert or delete on post
  54 for each row
  55 execute procedure site_aggregates_post();
  56
  57 -- comment
  58 create function site_aggregates_comment()
  59 returns trigger language plpgsql
  60 as $$
  61 begin
  62   IF (TG_OP = 'INSERT') THEN
  63     update site_aggregates
  64     set comments = comments + 1;
  65   ELSIF (TG_OP = 'DELETE') THEN
  66     update site_aggregates
  67     set comments = comments - 1;
  68   END IF;
  69   return null;
  70 end $$;
  71
  72 create trigger site_aggregates_comment
  73 after insert or delete on comment
  74 for each row
  75 execute procedure site_aggregates_comment();
  76
  77 -- community
  78 create function site_aggregates_community()
  79 returns trigger language plpgsql
  80 as $$
  81 begin
  82   IF (TG_OP = 'INSERT') THEN
  83     update site_aggregates
  84     set communities = communities + 1;
  85   ELSIF (TG_OP = 'DELETE') THEN
  86     update site_aggregates
  87     set communities = communities - 1;
  88   END IF;
  89   return null;
  90 end $$;
  91
  92 create trigger site_aggregates_community
  93 after insert or delete on community
  94 for each row
  95 execute procedure site_aggregates_community();
  96