5

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?

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

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

For Postgres 11: - Procedures were 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 only selected 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$  LANGUAGE plpgsql;

For 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$  LANGUAGE plpgsql;

The schema name is case sensitive in this context.
You need 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 explanation:

  • 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. – ypercubeᵀᴹ 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. – Erwin Brandstetter Oct 18 '18 at 23:42
3

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;
  • Oh, just saw your added answer. Yes, your additions all make sense. – Erwin Brandstetter 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 at 14:35

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.