Drop FUNCTION without knowing the number/type of parameters?

The art of crafting dynamic DROP FUNCTION commands

For dealing with a legion of clones (aka overloaded functions), generate dynamic DROP FUNCTION commands. This nifty PL/pgSQL block will round up each function and drop them, disregarding their parameters or schema:

DO $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN
    SELECT oid::regprocedure FROM pg_proc WHERE proname = 'your_function_name' AND pg_function_is_visible(oid)
  LOOP
    -- Sayonara, overload!
    EXECUTE 'DROP FUNCTION ' || rec.oid::regprocedure;
  END LOOP;
END
$$;


Navigating through the schema maze

The plot twist: you've got functions with the same name hiding in different schemas. Worry not, this script will pin down the correct function by qualifying the function name with its schema or by setting up a search light (filtering condition) based on the search path:

SELECT 'DROP FUNCTION ' || p.pronamespace::regnamespace || '.' || p.proid::regprocedure
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'your_function_name'
AND n.nspname = 'your_schema_name'
AND pg_function_is_visible(p.oid);


Dabbling with a dry-run

You don't want to blow things up, do you? Good, because the dry-run mode let you peek at the SQL commands without actually pulling the trigger. Output the generated SQL using NOTICE for a walk down memory lane during dry runs.

Flexibility derived from LIKE and regex

For those who like it flexible, swap out exact matches for LIKE syntax or regular expressions (regex). This gets you more elegant pattern matching when targeting functions to drop.

Harnessing the power of information schema

Where pg_proc blesses you with precise identification, information_schema.routines provides an easy-to-use alternative. However, brace yourself for the fact that information_schema does not x-ray function parameters as deeply as pg_proc.

The breadcrumb trail: NOTICE generated DDLs during execution

For full traceability and to know what hit the dust, spray RAISE NOTICE within your PL/pgSQL to echo out the DROP FUNCTION statement just before it's executed.

Key considerations and best practices

Pre-flight checks before dropping

I bet you wouldn't want a function apocalypse in the production environment. That's why it's crucial to test your script with overloaded functions in a safe sandbox environment.

Maintain database integrity and dependencies

When knocking off functions, consider that they might have dependencies. Treating a widely used function to a sudden demise may ruffle some feathers (disrupt processes and affect database integrity).

It's a case-sensitive world

In the PostgreSQL realm, be mindful of case sensitivity. Remember, unquoted identifiers are mapped to lower-case, so keep function names out of double quotes for case-sensitive handling.

Read, rinse, and repeat PostgreSQL documentation

For hands-on experience and deep dives into function manipulation in PostgreSQL, there's no better companion than the PostgreSQL documentation on DROP FUNCTION!