How to drop function from all schemas

Question

I would like to drop a function from all schemas in my Postgres db. I have ~200 schemas, (t0000001, t0000002, t0000003 ...) and I would really not like to do it manually.

Is there any way to do this?

score 2 · Accepted Answer · edited Apr 02 '15 at 12:21

2

You could try this:

do $$
declare s record;
begin
  for s in select schema_name from information_schema.schemata loop
    execute 'drop function if exists ' || s.schema_name || '.yourfunctionname()';
  end loop;
end; $$;

edited Apr 02 '15 at 12:21

Nick Barnes

19,816
3
51
63

answered Apr 02 '15 at 11:47

Gregor Raýman

3,051
13
19

score 2 · Answer 2 · edited May 23 '17 at 11:43

It would be very inefficient to loop through hundreds of schemas, while the function may just exist in a few of them.

Also DROP FUNCTION can drop all of them at once.

And Postgres supports function overloading, so any number of functions can exist with the same base name in the same schema but with different arguments. Per documentation:

The argument types to the function must be specified, since several different functions can exist with the same name and different argument lists.

Bold emphasis mine.

This removes all functions with the same base name that are visible to the caller (else he could not delete it anyway).
Careful with that!

CREATE OR REPLACE FUNCTION f_delfunc(_name text)
  RETURNS void AS
$func$
BEGIN

EXECUTE (
   SELECT string_agg(format('DROP FUNCTION %s(%s);'
                     ,oid::regproc
                     ,pg_catalog.pg_get_function_identity_arguments(oid))
          ,E'\n')
   FROM   pg_proc
   WHERE  proname = _name
   AND    pg_function_is_visible(oid));

END
$func$ LANGUAGE plpgsql;

How to drop function from all schemas

2 Answers2