9

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?

3
  • 5
    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 Dec 3 '15 at 2:44
  • I am reading on your given links. Thank you.
    – Sarit
    Dec 3 '15 at 2:52
  • 1
    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. Jan 19 '18 at 6:41
14

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$;
2
  • 1
    @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. Oct 12 '18 at 8:54
  • 1
    @ypercubeᵀᴹ: 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. Oct 18 '18 at 23:42
5

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;
3
  • Oh, just saw your added answer. Yes, your additions all make sense. Sep 12 '18 at 1:23
  • @ErwinBrandstetter sorry I was little too impatient :)
    – mehmet
    Sep 12 '18 at 15:26
  • pg_proc.proisagg is no longer with us with pg version 11
    – radiospiel
    Mar 6 '19 at 14:35
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.