DROP FUNCTION without knowing the number/type of parameters?

by

Basic query

This query creates all necessary DDL statements:

SELECT 'DROP FUNCTION ' || oid::regprocedure
FROM   pg_proc
WHERE  proname="my_function_name"  -- name without schema-qualification
AND    pg_function_is_visible(oid);  -- restrict to current search_path

Output:

DROP FUNCTION my_function_name(string text, form text, maxlen integer);
DROP FUNCTION my_function_name(string text, form text);
DROP FUNCTION my_function_name(string text);

Execute the commands after checking plausibility.

Pass the function name case-sensitive and with no added double-quotes to match against pg_proc.proname.

The cast to the object identifier type regprocedure (oid::regprocedure), and then to text implicitly, produces function names with argument types, automatically double-quoted and schema-qualified according to the current search_path where needed. No SQL injection possible.

pg_function_is_visible(oid) restricts the selection to functions in the current search_path (“visible”). You may or may not want this.

If you have multiple functions of the same name in multiple schemas, or overloaded functions with various function arguments, all of those will be listed separately. You may want to restrict to specific schema(s) or specific function parameter(s).

Related:

Function

You can build a plpgsql function around this to execute the statements immediately with EXECUTE. For Postgres 9.1 or later:
Careful! It drops your functions!

CREATE OR REPLACE FUNCTION f_delfunc(_name text, OUT functions_dropped int)
   LANGUAGE plpgsql AS
$func$
-- drop all functions with given _name in the current search_path, regardless of function parameters
DECLARE
   _sql text;
BEGIN
   SELECT count(*)::int
        , 'DROP FUNCTION ' || string_agg(oid::regprocedure::text, '; DROP FUNCTION ')
   FROM   pg_catalog.pg_proc
   WHERE  proname = _name
   AND    pg_function_is_visible(oid)  -- restrict to current search_path
   INTO   functions_dropped, _sql;     -- count only returned if subsequent DROPs succeed

   IF functions_dropped > 0 THEN       -- only if function(s) found
     EXECUTE _sql;
   END IF;
END
$func$;

Call:

SELECT f_delfunc('my_function_name');

The function returns the number of functions found and dropped if no exceptions are raised. 0 if none were found.

Further reading:

For Postgres versions older than 9.1 or older variants of the function using regproc and pg_get_function_identity_arguments(oid) check the edit history of this answer.

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. PostgreSQL parameterized Order By / Limit in table function
  3. COPY with dynamic file name
  4. Is SELECT or INSERT in a function prone to race conditions?
  5. Difference between language sql and language plpgsql in PostgreSQL functions
  6. How to return result of a SELECT inside a function in PostgreSQL?
  7. Pass multiple values in single parameter
  8. GROUP BY and aggregate sequential numeric values
  9. Safely rename tables using serial primary key columns
  10. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  11. PL/pgSQL checking if a row exists
  12. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  13. PostgreSQL IF statement
  14. Call a set-returning function with an array argument multiple times
  15. Postgres manually alter sequence
  16. Postgres Dynamic Query Function
  17. Return setof record (virtual table) from function
  18. Window Functions or Common Table Expressions: count previous rows within range
  19. SQL update fields of one table from fields of another one
  20. Postgres function returning table not returning data in columns
  21. PostgreSQL convert columns to rows? Transpose?
  22. Return multiple columns and rows from a function PostgreSQL instead of record
  23. PostgreSQL create table if not exists
  24. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  25. Create PostgreSQL ROLE (user) if it doesn’t exist
  26. pseudo_encrypt() function in plpgsql that takes bigint
  27. How to perform a select query in a DO block?
  28. Function with SQL query has no destination for result data
  29. Postgres trigger after insert accessing NEW
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment