How to Safely Drop All Overloaded Functions by Name in PostgreSQL?

Dropping Functions without Specified Parameter Numbers/Types

When managing functions stored in a text file, adding or modifying function parameters can create overloads. Deleting the original function requires manually listing all parameter types, which can be cumbersome.

Basic Query

To address this, the following query generates DDL statements to drop all functions with a given name, regardless of parameters:

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

Function

For immediate execution of the generated statements, the following PL/pgSQL function can be used:

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 the function as follows to drop all functions matching the specified name:

SELECT f_delfunc('my_function_name');

The function returns the number of functions dropped or 0 if none were found.

The above is the detailed content of How to Safely Drop All Overloaded Functions by Name in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!