migrations/2023-07-18-082614_post_aggregates_community_id/up.sql

   1 -- Your SQL goes here
   2 ALTER TABLE post_aggregates
   3     ADD COLUMN community_id integer REFERENCES community ON UPDATE CASCADE ON DELETE CASCADE,
   4     ADD COLUMN creator_id integer REFERENCES person ON UPDATE CASCADE ON DELETE CASCADE;
   5
   6 CREATE OR REPLACE FUNCTION post_aggregates_post()
   7     RETURNS trigger
   8     LANGUAGE plpgsql
   9 AS
  10 $$
  11 BEGIN
  12     IF (TG_OP = 'INSERT') THEN
  13         INSERT INTO post_aggregates (post_id,
  14                                      published,
  15                                      newest_comment_time,
  16                                      newest_comment_time_necro,
  17                                      community_id,
  18                                      creator_id)
  19         VALUES (NEW.id, NEW.published, NEW.published, NEW.published, NEW.community_id, NEW.creator_id);
  20     ELSIF (TG_OP = 'DELETE') THEN
  21         DELETE FROM post_aggregates WHERE post_id = OLD.id;
  22     END IF;
  23     RETURN NULL;
  24 END
  25 $$;
  26
  27 UPDATE post_aggregates
  28 SET community_id=post.community_id,
  29     creator_id=post.creator_id
  30 FROM post
  31 WHERE post.id = post_aggregates.post_id;
  32
  33 ALTER TABLE post_aggregates
  34     ALTER COLUMN community_id SET NOT NULL,
  35     ALTER COLUMN creator_id SET NOT NULL;