CODEWITHSUNDEEP
sqlpostgresqlplpgsqldynamic-sqlddl
Alexandr
Alexandr

Reputation: 726

How to drop function from all schemas

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?

Upvotes: 0

Views: 874

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659367

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;

Related answer with more details:

Upvotes: 2

Gregor Raýman
Gregor Raýman

Reputation: 3081

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; $$;

Upvotes: 2

Related Questions