Home >Database >Mysql Tutorial >How to Safely Drop All Overloaded Functions by Name in PostgreSQL?
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.
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
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!