migrations/2020-09-07-231141_add_migration_utils/up.sql

   1 CREATE SCHEMA utils;
   2
   3 CREATE TABLE utils.deps_saved_ddl (
   4     id serial NOT NULL,
   5     view_schema character varying(255),
   6     view_name character varying(255),
   7     ddl_to_run text,
   8     CONSTRAINT deps_saved_ddl_pkey PRIMARY KEY (id)
   9 );
  10
  11 CREATE OR REPLACE FUNCTION utils.save_and_drop_views (p_view_schema name, p_view_name name)
  12     RETURNS void
  13     LANGUAGE plpgsql
  14     COST 100
  15     AS $BODY$
  16 DECLARE
  17     v_curr record;
  18 BEGIN
  19     FOR v_curr IN (
  20         SELECT
  21             obj_schema,
  22             obj_name,
  23             obj_type
  24         FROM ( WITH RECURSIVE recursive_deps (
  25                 obj_schema,
  26                 obj_name,
  27                 obj_type,
  28                 depth
  29 ) AS (
  30                 SELECT
  31                     p_view_schema::name,
  32                     p_view_name,
  33                     NULL::varchar,
  34                     0
  35                 UNION
  36                 SELECT
  37                     dep_schema::varchar,
  38                     dep_name::varchar,
  39                     dep_type::varchar,
  40                     recursive_deps.depth + 1
  41                 FROM (
  42                     SELECT
  43                         ref_nsp.nspname ref_schema,
  44                         ref_cl.relname ref_name,
  45                         rwr_cl.relkind dep_type,
  46                         rwr_nsp.nspname dep_schema,
  47                         rwr_cl.relname dep_name
  48                     FROM
  49                         pg_depend dep
  50                         JOIN pg_class ref_cl ON dep.refobjid = ref_cl.oid
  51                         JOIN pg_namespace ref_nsp ON ref_cl.relnamespace = ref_nsp.oid
  52                         JOIN pg_rewrite rwr ON dep.objid = rwr.oid
  53                         JOIN pg_class rwr_cl ON rwr.ev_class = rwr_cl.oid
  54                         JOIN pg_namespace rwr_nsp ON rwr_cl.relnamespace = rwr_nsp.oid
  55                     WHERE
  56                         dep.deptype = 'n'
  57                         AND dep.classid = 'pg_rewrite'::regclass) deps
  58                     JOIN recursive_deps ON deps.ref_schema = recursive_deps.obj_schema
  59                         AND deps.ref_name = recursive_deps.obj_name
  60                 WHERE (deps.ref_schema != deps.dep_schema
  61                     OR deps.ref_name != deps.dep_name))
  62             SELECT
  63                 obj_schema,
  64                 obj_name,
  65                 obj_type,
  66                 depth
  67             FROM
  68                 recursive_deps
  69             WHERE
  70                 depth > 0) t
  71         GROUP BY
  72             obj_schema,
  73             obj_name,
  74             obj_type
  75         ORDER BY
  76             max(depth) DESC)
  77             LOOP
  78                 IF v_curr.obj_type = 'v' THEN
  79                     INSERT INTO utils.deps_saved_ddl (view_schema, view_name, ddl_to_run)
  80                     SELECT
  81                         p_view_schema,
  82                         p_view_name,
  83                         'CREATE VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || view_definition
  84                     FROM
  85                         information_schema.views
  86                     WHERE
  87                         table_schema = v_curr.obj_schema
  88                         AND table_name = v_curr.obj_name;
  89                     EXECUTE 'DROP VIEW' || ' ' || v_curr.obj_schema || '.' || v_curr.obj_name;
  90                 END IF;
  91             END LOOP;
  92 END;
  93 $BODY$;
  94
  95 CREATE OR REPLACE FUNCTION utils.restore_views (p_view_schema character varying, p_view_name character varying)
  96     RETURNS void
  97     LANGUAGE plpgsql
  98     COST 100
  99     AS $BODY$
 100 DECLARE
 101     v_curr record;
 102 BEGIN
 103     FOR v_curr IN (
 104         SELECT
 105             ddl_to_run,
 106             id
 107         FROM
 108             utils.deps_saved_ddl
 109         WHERE
 110             view_schema = p_view_schema
 111             AND view_name = p_view_name
 112         ORDER BY
 113             id DESC)
 114             LOOP
 115                 BEGIN
 116                     EXECUTE v_curr.ddl_to_run;
 117                     DELETE FROM utils.deps_saved_ddl
 118                     WHERE id = v_curr.id;
 119                 EXCEPTION
 120                     WHEN OTHERS THEN
 121                         -- keep looping, but please check for errors or remove left overs to handle manually
 122                 END;
 123     END LOOP;
 124 END;
 125
 126 $BODY$;
 127