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

   1 create schema utils;
   2
   3 create table utils.deps_saved_ddl
   4 (
   5   id serial NOT NULL,
   6   view_schema character varying(255),
   7   view_name character varying(255),
   8   ddl_to_run text,
   9   CONSTRAINT deps_saved_ddl_pkey PRIMARY KEY (id)
  10 );
  11
  12 create or replace function utils.save_and_drop_views(p_view_schema name, p_view_name name)
  13     RETURNS void
  14     LANGUAGE plpgsql
  15     COST 100
  16 AS $BODY$
  17
  18 declare
  19   v_curr record;
  20 begin
  21 for v_curr in
  22 (
  23   select obj_schema, obj_name, obj_type from
  24   (
  25   with recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as
  26   (
  27     select p_view_schema::name, p_view_name, null::varchar, 0
  28     union
  29     select dep_schema::varchar, dep_name::varchar, dep_type::varchar, recursive_deps.depth + 1 from
  30     (
  31       select ref_nsp.nspname ref_schema, ref_cl.relname ref_name,
  32       rwr_cl.relkind dep_type,
  33       rwr_nsp.nspname dep_schema,
  34       rwr_cl.relname dep_name
  35       from pg_depend dep
  36       join pg_class ref_cl on dep.refobjid = ref_cl.oid
  37       join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
  38       join pg_rewrite rwr on dep.objid = rwr.oid
  39       join pg_class rwr_cl on rwr.ev_class = rwr_cl.oid
  40       join pg_namespace rwr_nsp on rwr_cl.relnamespace = rwr_nsp.oid
  41       where dep.deptype = 'n'
  42       and dep.classid = 'pg_rewrite'::regclass
  43     ) deps
  44     join recursive_deps on deps.ref_schema = recursive_deps.obj_schema and deps.ref_name = recursive_deps.obj_name
  45     where (deps.ref_schema != deps.dep_schema or deps.ref_name != deps.dep_name)
  46   )
  47   select obj_schema, obj_name, obj_type, depth
  48   from recursive_deps
  49   where depth > 0
  50   ) t
  51   group by obj_schema, obj_name, obj_type
  52   order by max(depth) desc
  53 ) loop
  54   if v_curr.obj_type = 'v' then
  55     insert into utils.deps_saved_ddl(view_schema, view_name, ddl_to_run)
  56     select p_view_schema, p_view_name, 'CREATE VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || view_definition
  57     from information_schema.views
  58     where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
  59
  60     execute 'DROP VIEW' || ' ' || v_curr.obj_schema || '.' || v_curr.obj_name;
  61   end if;
  62 end loop;
  63 end;
  64 $BODY$;
  65
  66 create or replace function utils.restore_views(p_view_schema character varying, p_view_name character varying)
  67   RETURNS void
  68   LANGUAGE plpgsql
  69   COST 100
  70 AS $BODY$
  71 declare
  72   v_curr record;
  73 begin
  74 for v_curr in
  75 (
  76   select ddl_to_run, id
  77   from utils.deps_saved_ddl
  78   where view_schema = p_view_schema and view_name = p_view_name
  79   order by id desc
  80 ) loop
  81 begin
  82   execute v_curr.ddl_to_run;
  83   delete from utils.deps_saved_ddl where id = v_curr.id;
  84   EXCEPTION WHEN OTHERS THEN
  85       -- keep looping, but please check for errors or remove left overs to handle manually
  86           end;
  87 end loop;
  88 end;
  89 $BODY$;