PostgreSQL – How to Drop All Functions

dynamic-sqlfunctionspostgresql

Right now I have to use query to get the command in text file.
Then remove double quote from them. And finally, run that file in psql shell.

How can drop all of my functions in PostgreSQL in a single step?

Best Answer

To drop all functions (including aggregates) in a given schema (be careful with this!):

Postgres 11 or later

  • Procedures have been added.
  • The system catalog pg_proc slightly changed: prokind replaces proisagg and proiswindow - and also tags functions and the new procedures
DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          string_agg(format('DROP %s %s;'
                          , CASE prokind
                              WHEN 'f' THEN 'FUNCTION'
                              WHEN 'a' THEN 'AGGREGATE'
                              WHEN 'p' THEN 'PROCEDURE'
                              WHEN 'w' THEN 'FUNCTION'  -- window function (rarely applicable)
                              -- ELSE NULL              -- not possible in pg 11
                            END
                          , oid::regprocedure)
                   , E'\n')
   FROM   pg_proc
   WHERE  pronamespace = 'public'::regnamespace  -- schema name here!
   -- AND    prokind = ANY ('{f,a,p,w}')         -- optionally filter kinds
   ;

   IF _sql IS NOT NULL THEN
      RAISE NOTICE '%', _sql;  -- debug / check first
      -- EXECUTE _sql;         -- uncomment payload once you are sure
   ELSE 
      RAISE NOTICE 'No fuctions found in schema %', quote_ident(_schema);
   END IF;
END
$do$;

The schema name is case sensitive in this context.
The executing role needs to have the necessary privileges of course.

You might add CASCADE like demonstrated by mehmet but that will also drop depending objects, recursively - not just functions. Makes it even more dangerous. You better know exactly what you are doing.

Related, with more explanation:

Postgres 10 or older

DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          string_agg(format('DROP %s %s;'
                          , CASE WHEN proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
                          , oid::regprocedure)
                   , E'\n')
   FROM   pg_proc
   WHERE  pronamespace = 'public'::regnamespace;  -- schema name here!

   IF _sql IS NOT NULL THEN
      RAISE NOTICE '%', _sql;  -- debug / check first
      -- EXECUTE _sql;         -- uncomment payload once you are sure
   ELSE 
      RAISE NOTICE 'No fuctions found in schema %', quote_ident(_schema);
   END IF;
END
$do$;
Related Question