How to drop all of my functions in PostgreSQL?

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?

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:

• How to drop function from all schemas

• DROP FUNCTION without knowing the number/type of parameters?

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


StackExchange Database Administrators Q#122742, answer score: 20