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 BEGIN
  11     IF (TG_OP = 'INSERT') THEN
  12         INSERT INTO post_aggregates (post_id, published, newest_comment_time, newest_comment_time_necro, community_id, creator_id)
  13             VALUES (NEW.id, NEW.published, NEW.published, NEW.published, NEW.community_id, NEW.creator_id);
  14     ELSIF (TG_OP = 'DELETE') THEN
  15         DELETE FROM post_aggregates
  16         WHERE post_id = OLD.id;
  17     END IF;
  18     RETURN NULL;
  19 END
  20 $$;
  21
  22 UPDATE
  23     post_aggregates
  24 SET
  25     community_id = post.community_id,
  26     creator_id = post.creator_id
  27 FROM
  28     post
  29 WHERE
  30     post.id = post_aggregates.post_id;
  31
  32 ALTER TABLE post_aggregates
  33     ALTER COLUMN community_id SET NOT NULL,
  34     ALTER COLUMN creator_id SET NOT NULL;
  35