migrations/2022-11-20-032430_sticky_local/down.sql

   1 DROP TRIGGER IF EXISTS post_aggregates_featured_local ON post;
   2
   3 DROP TRIGGER IF EXISTS post_aggregates_featured_community ON post;
   4
   5 DROP FUNCTION post_aggregates_featured_community;
   6
   7 DROP FUNCTION post_aggregates_featured_local;
   8
   9 ALTER TABLE post
  10     ADD stickied boolean NOT NULL DEFAULT FALSE;
  11
  12 UPDATE
  13     post
  14 SET
  15     stickied = featured_community;
  16
  17 ALTER TABLE post
  18     DROP COLUMN featured_community;
  19
  20 ALTER TABLE post
  21     DROP COLUMN featured_local;
  22
  23 ALTER TABLE post_aggregates
  24     ADD stickied boolean NOT NULL DEFAULT FALSE;
  25
  26 UPDATE
  27     post_aggregates
  28 SET
  29     stickied = featured_community;
  30
  31 ALTER TABLE post_aggregates
  32     DROP COLUMN featured_community;
  33
  34 ALTER TABLE post_aggregates
  35     DROP COLUMN featured_local;
  36
  37 ALTER TABLE mod_feature_post RENAME COLUMN featured TO stickied;
  38
  39 ALTER TABLE mod_feature_post
  40     DROP COLUMN is_featured_community;
  41
  42 ALTER TABLE mod_feature_post
  43     ALTER COLUMN stickied DROP NOT NULL;
  44
  45 ALTER TABLE mod_feature_post RENAME TO mod_sticky_post;
  46
  47 CREATE FUNCTION post_aggregates_stickied ()
  48     RETURNS TRIGGER
  49     LANGUAGE plpgsql
  50     AS $$
  51 BEGIN
  52     UPDATE
  53         post_aggregates pa
  54     SET
  55         stickied = NEW.stickied
  56     WHERE
  57         pa.post_id = NEW.id;
  58     RETURN NULL;
  59 END
  60 $$;
  61
  62 CREATE TRIGGER post_aggregates_stickied
  63     AFTER UPDATE ON post
  64     FOR EACH ROW
  65     WHEN (OLD.stickied IS DISTINCT FROM NEW.stickied)
  66     EXECUTE PROCEDURE post_aggregates_stickied ();
  67