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?
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'DROPFUNCTION'|| ns.nspname ||'.'|| proname ||'('||oidvectortypes(proargtypes) ||');'FROM pg_proc INNERJOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE ns.nspname ='my_messed_up_schema'orderby proname;
The above will generate but not execute code that looks something like below so you can inspect the drops before executing: