How to drop all of my functions in PostgreSQL?

Question

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?

• Craig Ringer: Don't use raw ||. Use quote_ident or the format function with the %I format-specifier. That way identifier quoting is handled properly. Use a DO block to EXECUTE the queries you generate to avoid doing it in multiple steps. See dynamic-sql, postgresql
• sarit: I am reading on your given links. Thank you.
• Evan Carroll: Removed the lengthy steps as they're relatively irrelevant. Also edited the title, they're not "your" functions unless you're testing for ownership. They're just functions in a schema that you can drop.

Answer 1

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

• ypercubeᵀᴹ: @ErwinBrandstetter do you a similar method to drop all custom types? I could post a question, if you haven't answered this, here or at SO.
• Erwin Brandstetter: @ypercubeTM: A similar procedure should get it done. But you need to define "custom type". Only from a given schema? Row types of user tables would qualify as "custom types", too, but cannot be dropped while the table depends on it. I suggest you start a new question with defining info.

Answer 2

I had to touch-up Erwin's answer above for several cases:

1) If you had an aggregate function in the schema that triggered and error. 2) If you wanted to drop functions in two schemas and the function that deletes is in the first, it got deleted too. 3) If functions depended on each other. Use cascade to force-drop.

Here it is:

create or replace function data.delete_all_functions(schema_in text)
 returns void as
$$
declare
 qry text;
begin
 select into qry string_agg(
 format(
 case when proname = 'delete_all_functions' then '-- %s;' -- don't delete self
 when proisagg then 'drop aggregate if exists %s cascade;'
 else 'drop function if exists %s cascade;'
 end,
 oid :: regprocedure
 ),
 E'\n'
 )
 from pg_proc
 where pronamespace = schema_in :: regnamespace;
 if qry is not null then
 execute qry;
 raise notice 'deleted all functions in schema: %', schema_in;
 else
 raise notice 'no functions to delete in schema: %', schema_in;
 end if;
end
$$
language plpgsql;

• Erwin Brandstetter: Oh, just saw your added answer. Yes, your additions all make sense.
• mehmet: @ErwinBrandstetter sorry I was little too impatient :)
• radiospiel: pg_proc.proisagg is no longer with us with pg version 11

Answer 3

I took the correct answer above and made it into a function that generated the script passing in the schema name and a search pattern as params. I keep a few of these in my db during design phase but either lock them down or remove them from production.

CREATE FUNCTION extfunc.systool_generatescript_functiondelete(
 schemaname character varying,
 searchpattern character varying)
 RETURNS character varying
 LANGUAGE 'plpgsql'
 COST 100
 VOLATILE 
 
AS $BODY$
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 = schemaname::regnamespace -- schema name here!
 -- AND prokind = ANY ('{f,a,p,w}') -- optionally filter kinds
 AND oid::regprocedure::varchar LIKE ('%' || searchpattern || '%');
 RETURN _sql;
 
END;
$BODY$;

Answer 4

The one-step variants are hard to understand, so here are simple steps on HeidiSQL, but I hope you can do it on any DB viewer. Make a table that generates queries to delete functions

SELECT 'DROP FUNCTION "' || p.proname || '";'
FROM 
 pg_catalog.pg_namespace n
JOIN 
 pg_catalog.pg_proc p ON 
 p.pronamespace = n.oid
WHERE 
 p.prokind = 'f'
AND
 n.nspname = 'public';

or

SELECT 
 'DROP FUNCTION "' || ROUTINE_NAME || '";'
FROM 
 information_schema.routines
WHERE 
 routine_type = 'FUNCTION'
AND
 routine_schema = 'public';

So here you see some rows:

DROP FUNCTION "a_i_aspnetuserclaims_f";
DROP FUNCTION "a_d_aspnetroleclaims_f";
DROP FUNCTION "a_d_aspnetuserclaims_f";
DROP FUNCTION "a_d_channels_f";
DROP FUNCTION "a_d_aspnetuserlogins_f";

Select all rows, then click "Export" as text on your buffer. So you could insert it to execute: enter image description here

• Sérgio: I liked this approx : ``` SELECT 'DROP FUNCTION ' || p.proname || '(' || oidvectortypes(proargtypes) ||');' FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE p.prokind = 'f' AND n.nspname = 'public'; ``` works even better