Skip to content

Instantly share code, notes, and snippets.

@mateuszwenus

mateuszwenus/save_restore_dependencies.sql

Last active November 13, 2025 07:30
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save mateuszwenus/11187288 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save mateuszwenus/11187288 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. mateuszwenus revised this gist Apr 28, 2014. 1 changed file with 7 additions and 13 deletions.
    20 changes: 7 additions & 13 deletions save_restore_dependencies.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -18,25 +18,19 @@ for v_curr in
    with recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as
    (
    select p_view_schema, p_view_name, null::varchar, 0
    union all
    union
    select dep_schema::varchar, dep_name::varchar, dep_type::varchar, recursive_deps.depth + 1 from
    (
    select ref_nsp.nspname ref_schema, ref_cl.relname ref_name,
    case
    when dep.classid = 'pg_rewrite'::regclass then (select cl.relkind from pg_rewrite rw join pg_class cl on rw.ev_class = cl.oid where rw.oid = dep.objid)
    else null
    end::varchar dep_type,
    case
    when dep.classid = 'pg_rewrite'::regclass then (select nsp.nspname from pg_rewrite rw join pg_class cl on rw.ev_class = cl.oid join pg_namespace nsp on cl.relnamespace = nsp.oid where rw.oid = dep.objid)
    else null
    end::varchar dep_schema,
    case
    when dep.classid = 'pg_rewrite'::regclass then (select substring(ev_class::regclass::varchar, 1 + position('.' in ev_class::regclass::varchar)) from pg_rewrite where oid = dep.objid)
    else null
    end dep_name
    rwr_cl.relkind dep_type,
    rwr_nsp.nspname dep_schema,
    rwr_cl.relname dep_name
    from pg_depend dep
    join pg_class ref_cl on dep.refobjid = ref_cl.oid
    join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
    join pg_rewrite rwr on dep.objid = rwr.oid
    join pg_class rwr_cl on rwr.ev_class = rwr_cl.oid
    join pg_namespace rwr_nsp on rwr_cl.relnamespace = rwr_nsp.oid
    where dep.deptype = 'n'
    and dep.classid = 'pg_rewrite'::regclass
    ) deps
  2. mateuszwenus revised this gist Apr 23, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion save_restore_dependencies.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -31,7 +31,7 @@ for v_curr in
    else null
    end::varchar dep_schema,
    case
    when dep.classid = 'pg_rewrite'::regclass then (select ev_class::regclass::varchar from pg_rewrite where oid = dep.objid)
    when dep.classid = 'pg_rewrite'::regclass then (select substring(ev_class::regclass::varchar, 1 + position('.' in ev_class::regclass::varchar)) from pg_rewrite where oid = dep.objid)
    else null
    end dep_name
    from pg_depend dep
  3. mateuszwenus renamed this gist Apr 22, 2014. 1 changed file with 0 additions and 0 deletions.
    0 save_restore_dependencies → save_restore_dependencies.sql
    View file
    File renamed without changes.
  4. mateuszwenus created this gist Apr 22, 2014.
    122 changes: 122 additions & 0 deletions save_restore_dependencies
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,122 @@
    create table deps_saved_ddl
    (
    deps_id serial primary key,
    deps_view_schema varchar(255),
    deps_view_name varchar(255),
    deps_ddl_to_run text
    );

    create or replace function deps_save_and_drop_dependencies(p_view_schema varchar, p_view_name varchar) returns void as
    $$
    declare
    v_curr record;
    begin
    for v_curr in
    (
    select obj_schema, obj_name, obj_type from
    (
    with recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as
    (
    select p_view_schema, p_view_name, null::varchar, 0
    union all
    select dep_schema::varchar, dep_name::varchar, dep_type::varchar, recursive_deps.depth + 1 from
    (
    select ref_nsp.nspname ref_schema, ref_cl.relname ref_name,
    case
    when dep.classid = 'pg_rewrite'::regclass then (select cl.relkind from pg_rewrite rw join pg_class cl on rw.ev_class = cl.oid where rw.oid = dep.objid)
    else null
    end::varchar dep_type,
    case
    when dep.classid = 'pg_rewrite'::regclass then (select nsp.nspname from pg_rewrite rw join pg_class cl on rw.ev_class = cl.oid join pg_namespace nsp on cl.relnamespace = nsp.oid where rw.oid = dep.objid)
    else null
    end::varchar dep_schema,
    case
    when dep.classid = 'pg_rewrite'::regclass then (select ev_class::regclass::varchar from pg_rewrite where oid = dep.objid)
    else null
    end dep_name
    from pg_depend dep
    join pg_class ref_cl on dep.refobjid = ref_cl.oid
    join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
    where dep.deptype = 'n'
    and dep.classid = 'pg_rewrite'::regclass
    ) deps
    join recursive_deps on deps.ref_schema = recursive_deps.obj_schema and deps.ref_name = recursive_deps.obj_name
    where (deps.ref_schema != deps.dep_schema or deps.ref_name != deps.dep_name)
    )
    select obj_schema, obj_name, obj_type, depth
    from recursive_deps
    where depth > 0
    ) t
    group by obj_schema, obj_name, obj_type
    order by max(depth) desc
    ) loop

    insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    select p_view_schema, p_view_name, 'COMMENT ON ' ||
    case
    when c.relkind = 'v' then 'VIEW'
    when c.relkind = 'm' then 'MATERIALIZED VIEW'
    else ''
    end
    || ' ' || n.nspname || '.' || c.relname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
    from pg_class c
    join pg_namespace n on n.oid = c.relnamespace
    join pg_description d on d.objoid = c.oid and d.objsubid = 0
    where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;

    insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    select p_view_schema, p_view_name, 'COMMENT ON COLUMN ' || n.nspname || '.' || c.relname || '.' || a.attname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
    from pg_class c
    join pg_attribute a on c.oid = a.attrelid
    join pg_namespace n on n.oid = c.relnamespace
    join pg_description d on d.objoid = c.oid and d.objsubid = a.attnum
    where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;

    insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    select p_view_schema, p_view_name, 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || ' TO ' || grantee
    from information_schema.role_table_grants
    where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;

    if v_curr.obj_type = 'v' then
    insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    select p_view_schema, p_view_name, 'CREATE VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || view_definition
    from information_schema.views
    where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
    elsif v_curr.obj_type = 'm' then
    insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    select p_view_schema, p_view_name, 'CREATE MATERIALIZED VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || definition
    from pg_matviews
    where schemaname = v_curr.obj_schema and matviewname = v_curr.obj_name;
    end if;

    execute 'DROP ' ||
    case
    when v_curr.obj_type = 'v' then 'VIEW'
    when v_curr.obj_type = 'm' then 'MATERIALIZED VIEW'
    end
    || ' ' || v_curr.obj_schema || '.' || v_curr.obj_name;

    end loop;
    end;
    $$
    LANGUAGE plpgsql;

    create or replace function deps_restore_dependencies(p_view_schema varchar, p_view_name varchar) returns void as
    $$
    declare
    v_curr record;
    begin
    for v_curr in
    (
    select deps_ddl_to_run
    from deps_saved_ddl
    where deps_view_schema = p_view_schema and deps_view_name = p_view_name
    order by deps_id desc
    ) loop
    execute v_curr.deps_ddl_to_run;
    end loop;
    delete from deps_saved_ddl
    where deps_view_schema = p_view_schema and deps_view_name = p_view_name;
    end;
    $$
    LANGUAGE plpgsql;