I'm not a PostgreSQL expert. Don't run this code without fully understanding what it does. My explanation may be wrong.

I had to come up with a way to delete all user-defined routines in a given database – routines being "aggregate functions, normal functions, and procedures". I thought I'd share the solution and what I've learned along the way.

First, we need to find all user-defined routines:

SELECT p.proname AS routine_name,
       pg_get_function_arguments(p.oid) AS routine_arguments
FROM pg_proc p
LEFT JOIN pg_namespace n on p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema');

The logic is borrowed from here under "Universal" but I've simplified and modified the query to fit my purposes. It will return a table such as the following:

 routine_name | routine_arguments 
--------------+-------------------
 foo          | a text, b text
 bar          | a integer
(2 rows)

If you need to distinguish between different types of routines, check the same source under "PostgreSQL 11+" and make modifications accordingly.

Now we can transform each row into a DROP ROUTINE query. routine_name and routine_arguments are the two pieces of information needed to do that. We surround the previous query with another:

DO $$
DECLARE
  drop_query TEXT;
BEGIN
  FOR drop_query IN SELECT 'DROP ROUTINE ' || routine_name || '(' || routine_arguments || ') CASCADE;' FROM
  (SELECT p.proname AS routine_name,
          pg_get_function_arguments(p.oid) AS routine_arguments
   FROM pg_proc p
   LEFT JOIN pg_namespace n on p.pronamespace = n.oid
   WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')) AS arbitrary_alias
  LOOP
    BEGIN
      EXECUTE drop_query;
    EXCEPTION
      WHEN null_value_not_allowed THEN
        RAISE NOTICE 'Already deleted';
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

In short, given the user-defined routines foo(a text, b text) and bar(a integer), the code above will create and execute the following queries:

DROP ROUTINE foo(a text, b text) CASCADE;
DROP ROUTINE bar(a integer) CASCADE;

Each of those queries is created by extracting information from the sub-query discussed in the beginning and then concatenating that information. (The || operator does not mean 'or' like in many other languages – it's a concatenation operator. One could also use CONCAT().)

The 'trick' is that the concatenation will occur for each row returned by the subquery. Thus, the table...

 routine_name | routine_arguments
--------------+-------------------
 foo          | a text, b text
 bar          | a integer
(2 rows)

...is transformed into...

                ?column?                                     
-------------------------------------------
 DROP ROUTINE foo(a text, b text) CASCADE;
 DROP ROUTINE bar(a integer) CASCADE;
(2 rows)

All the code then has to do is iterate over these queries and execute them.

The where clause in the sub-query filters the routines down to user-defined ones.

I've also added arbitrary_alias for no reason other than that PostgreSQL requires it.

Lastly, I've added a CASCADE instruction to the generated drop queries so that dependent routines are deleted along with their corresponding dependency routines. (They'd eventually be deleted anyway, given the universal nature of the code above, but PostgreSQL won't delete routines with dependents without the cascade instruction.) If a dependency has already been deleted, its corresponding drop query, found later on in the list, is then NULL. I do not know why – I expected the query to remain unchanged but fail – but the exception handling catches it in any case so the loop can keep going. Another thing I do not yet understand is why regular functions do not seem to be affected by this restriction/do not require cascading, whereas aggregate functions do. PostgreSQL lets me delete regular functions with dependents without problems, but not aggregate functions with dependents.¹

Note that, when dropping a routine, its arguments usually need not be specified. For example, given a routine named foo, the query DROP ROUTINE foo(); will do the job, with or without parentheses. However, for polymorphic/overloaded routines – i.e. routines with the same name but different signatures – the arguments need to be specified or PostgreSQL won't know which one you're referring to and will subsequently refuse to delete either one. For example, given the overloaded routine foo with signatures foo(bar text) and foo(bar text, baz text), the query DROP ROUTINE foo(bar text); will delete the former but not the latter. Since specifying the arguments does no harm for non-overloaded routines, I always specify them to make drop statements work for both overloaded and regular routines.