Drop all functions in a namespace? (Execute generated DDL commands?)

Question

I'm trying to write a command that will delete all functions in a namespace. I've already found a command that will generate the drop functions script:

SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname || '('
     || oidvectortypes(proargtypes) || ');'
FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE ns.nspname = 'public'  order by proname;

Source: http://www.postgresonline.com/journal/archives/74-How-to-delete-many-functions.html

This will generate something like:

                 ?column?                 
------------------------------------------
 DROP FUNCTION public.function1(bigint);
 DROP FUNCTION public.function2();
 DROP FUNCTION public.function3(text);

However, I can't figure out how to change the code, so that the functions are actually deleted - as opposed to only generating the commands.

Any ideas?

Answer 1

The system catalogs changed in Postgres 11! (prokind instead of proisagg) See:

• How to drop all of my functions in PostgreSQL?

---

Could look like this:

CREATE OR REPLACE FUNCTION public.f_delfunc(_schema text, _del text = '')
  RETURNS text AS
$func$
DECLARE
   _sql   text;
   _ct    text;
BEGIN
   SELECT INTO _sql, _ct
          string_agg('DROP '
                   || CASE p.proisagg WHEN true THEN 'AGGREGATE '
                                                ELSE 'FUNCTION ' END
                   || quote_ident(n.nspname) || '.' || quote_ident(p.proname)
                   || '('
                   || pg_catalog.pg_get_function_identity_arguments(p.oid)
                   || ')'
                    , E'\n'
          )
          ,count(*)::text
   FROM   pg_catalog.pg_proc p
   LEFT   JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
   WHERE  n.nspname = _schema;
   -- AND p.proname ~~* 'f_%';                     -- Only selected funcs?
   -- AND pg_catalog.pg_function_is_visible(p.oid) -- Only visible funcs?

   IF _ct = '0' THEN
      RETURN 'Found 0 functions to delete';
   ELSIF lower(_del) = 'del' THEN                        -- Actually delete!
      EXECUTE _sql;
      RETURN _ct || E' functions deleted:\n' || _sql;
   ELSE                                               -- Else only show SQL.
      RETURN _ct || E' functions to delete:\n' || _sql;
   END IF;
END
$func$  LANGUAGE plpgsql;

Call to show:

SELECT f_delfunc('public');         -- 2nd parameter is covered by default.

Call to delete:

SELECT f_delfunc('public','del');

Major points

• You need dynamic SQL for that. Use a plpgsql function or a DO statement (PostgreSQL 9.0+) with EXECUTE.

• Note the use of the functions pg_get_function_identity_arguments() and pg_function_is_visible. The latter can be omitted. It's a safeguard so you don't delete functions outside of the current user's search_path.

• I added a "safe mode". Only delete if $2 = 'del'. Else only show generated SQL.

• Be aware that the function will delete itself if it lives in the schema you delete from.

• I also added quote_ident() to safeguard against SQLi. Consider the following:

CREATE FUNCTION "; DELETE FROM users;"()
  RETURNS int AS
'SELECT 1'
  LANGUAGE sql;

• This fails if there are dependencies on any involved function. May be resolved by adding CASCADE, but I did not do that here, since it makes the function more dangerous, yet.

Related:

• DROP FUNCTION without knowing the number/type of parameters?
• How to get function parameter lists (so I can drop a function)

Answer 2

My Version without a stored procedure

DO $$DECLARE command text;
BEGIN
command = (SELECT 'DROP FUNCTION ' || proname || '(' || oidvectortypes(proargtypes) || ')'
FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE proname='functioniliketodrop'
order by proname);
execute command;    
END$$;

Answer 3

-- DROP FUNCTION public.f_deleteAllFunctions();

CREATE OR REPLACE FUNCTION public.f_deleteAllFunctions()
  RETURNS TABLE(functiondef character varying) AS
$BODY$ 

DECLARE 
var_r record;
var_query TEXT;

BEGIN

FOR var_r IN(
        SELECT  ns.nspname || '.' || proname || '(' || oidvectortypes(proargtypes) || ');' as nombreFuncion
        FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
        WHERE ns.nspname = 'public'  order by proname
        )

    LOOP
        functionDef := 'DROP FUNCTION ' ||var_r.nombreFuncion;
        RAISE NOTICE '%', functionDef;
        EXECUTE functionDef;
        RETURN NEXT;
    END LOOP;


END 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

ALTER FUNCTION public.f_deleteAllFunctions()
  OWNER TO postgres;

select * from f_deleteAllFunctions();

Answer 4

Just COPY+PASTE the output of your query, into your psql interpreter. It will run all commands you paste.

Answer 5

For any SQL expression that generates a set of commands:

begin;
create function _execute(text) returns boolean language plpgsql as $$
  begin
    raise info 'Execute: %', $1;
    execute $1;
  end;
$$;
select count(_execute(__SQL__)); -- __SQL__ is your command-generating statement
drop function _execute(text);
end;