Postgresql - Drop Extension from Specific Schema

Question

I currently have some databases with multiple copies of extensions installed. Specifically, there are multiple copies of the tablefunc functions that I would like to remove only from the public schema. Consider the following database structure:

extensions schema
  functions
    colpivot(...
    crosstab(...
    ...
public schema
  functions
    colpivot(...
    crosstab(...
    ...

How can I remove all tablefunc extension functions from just the public schema?
DROP EXTENSION tablefunc; in my case just removes the functions from the extensions schema (opposite of what I want to achieve). Of course this is proliferated through multiple databases and EXTENSIONS, so I'd like a script to target the extension name and not hard-code all the various DROP FUNCTION... commands.

One small question: how did you create several copies of the extension? Newer tried it before but when I try to create already existing extension in another schema, PostgreSQL says: "ERROR: extension "tablefunc" already exists" — Abelisto
– Abelisto, Commented Nov 24, 2017 at 21:14

Abelisto · Accepted Answer · 2017-11-25 00:20:15Z

Not the answer actually but here is where you can to start:

with obj_list as (
  select
    case when p.oid is not null then 'p' when t.oid is not null then 't' end as tp,
    case when p.oid is not null then p.oid::regprocedure::text when t.oid is not null then t.typname end as def,
    d.objid
  from pg_depend d left join pg_proc p on (d.objid = p.oid) left join pg_type t on (d.objid = t.oid)
  where refobjid = (select oid from pg_extension where extname = 'tablefunc' /* extension name here */ ))
select
  case tp when 't' then 'drop type ' when 'p' then 'drop function ' end || 'public.' || def || ';' as drop_stmt
from obj_list
order by objid desc /* reverse object creation order to saticfy dependencies */;

Output:

┌──────────────────────────────────────────────────────────────────────────────┐
│                                  drop_stmt                                   │
╞══════════════════════════════════════════════════════════════════════════════╡
│ drop function public.connectby(text,text,text,text,text,integer);            │
│ drop function public.connectby(text,text,text,text,text,integer,text);       │
│ drop function public.connectby(text,text,text,text,integer);                 │
│ drop function public.connectby(text,text,text,text,integer,text);            │
│ drop function public.crosstab(text,text);                                    │
│ drop function public.crosstab(text,integer);                                 │
│ drop function public.crosstab4(text);                                        │
│ drop function public.crosstab3(text);                                        │
│ drop function public.crosstab2(text);                                        │
│ drop type public.tablefunc_crosstab_4;                                       │
│ drop type public.tablefunc_crosstab_3;                                       │
│ drop type public.tablefunc_crosstab_2;                                       │
│ drop function public.crosstab(text);                                         │
│ drop function public.normal_rand(integer,double precision,double precision); │
└──────────────────────────────────────────────────────────────────────────────┘

Collectives™ on Stack Overflow

Postgresql - Drop Extension from Specific Schema

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related