How to delete many functions

There are a lot of functions in PostgreSQL and for the most part, they are nicely tucked away in the pg_catalog schema so they don't get mixed with your functions. There are a lot of contrib modules though and if you use a few of the big ones and just dump them in the public schema, the function list gets overwhelming and you have a hard time finding your own functions. To keep our sanity we tend to create a schema called util or something like that where we stuff our own personal functions for easy navigation and for larger contribs, we may put these in a separate schema altogether. Every once in a while, we screw up and put the functions in the wrong schema. Deleting these can become painful if there are a lot to delete.

Problem: How do you delete a butt load of functions without working up a sweat?

Solution:

Use the below code cautiously. We start off with our general hack of writing an sql statement to write lots of sql statements. We had hoped to use the more generic information_schema for this exercise, but the closest table we could find information_schema.routines, lists the names of the functions but not the arguments. You need the arguments in your drop since PostgreSQL supports function argument overloading.


SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname || '(' || oidvectortypes(proargtypes) || ');'
FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE ns.nspname = 'my_messed_up_schema'  order by proname;


The above will generate but not execute code that looks something like below so you can inspect the drops before executing:


DROP FUNCTION my_messed_up_schema.funcabc(int4,int4);
DROP FUNCTION my_messed_up_schema.funcdef(int4,date);