Can I Drop All Functions Matching a Wildcard Name in PostgreSQL, Regardless of Their Parameters?

Can I Drop Functions with a Wildcard, Regardless of Parameters?

Introduction

Maintaining functions stored in a text file can become cumbersome, especially when adding or removing parameters. Manually typing the exact parameter order for each function can be tedious. This article explores a solution for dropping all functions with a given name using a wildcard, eliminating the need to specify parameters.

Query for Dropping Functions

To create the necessary DDL statements, execute the following query:

SELECT 'DROP FUNCTION ' || oid::regprocedure
FROM pg_proc
WHERE proname = 'my_function_name'  -- Replace 'my_function_name' with the target function name
AND pg_function_is_visible(oid);  -- Restrict to the current search path

The query produces DROP FUNCTION statements with argument types, double-quoted and schema-qualified. This ensures no SQL injection vulnerability and matches the current search path.

Function for Executing Drop Statements

For immediate execution of the DROP statements, create a PL/pgSQL function:

CREATE OR REPLACE FUNCTION f_delfunc(_name text, OUT functions_dropped int)
LANGUAGE plpgsql AS
$func$
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)
  INTO functions_dropped, _sql;
  IF functions_dropped > 0 THEN
    EXECUTE _sql;
  END IF;
END
$func$;

Call the function with the desired function name to drop the matching functions and return the number dropped:

SELECT f_delfunc('my_function_name');

Additional Considerations

• This method drops all functions with the given name, regardless of parameters or schema.
• Use caution when executing this query, as it can permanently remove functions.
• For Postgres versions before 9.1, use regproc and pg_get_function_identity_arguments(oid) instead of regprocedure and the function string_agg.

The above is the detailed content of Can I Drop All Functions Matching a Wildcard Name in PostgreSQL, Regardless of Their Parameters?. For more information, please follow other related articles on the PHP Chinese website!