Postgres has a dedicated function for that purpose. Introduced with Postgres 8.4. The manual:
pg_get_function_identity_arguments(func_oid)
… get argument list to identify a function (without default values) …
pg_get_function_identity_arguments
returns the argument list
necessary to identify a function, in the form it would need to appear
in withinALTER FUNCTION
, for instance. This form omits default values.
Using that (and format()
, introduced with Postgres 9.1), the following query generates DDL statements to drop functions matching your search terms:
SELECT format('DROP %s %I.%I(%s);'
, CASE WHEN p.proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
, n.nspname
, p.proname
, pg_catalog.pg_get_function_identity_arguments(p.oid)
) AS stmt
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname="dblink" -- function name
-- AND n.nspname="public" -- schema name (optional)
-- AND pg_catalog.pg_function_is_visible(p.oid) -- function visible to user
ORDER BY 1;
The system catalog pg_proc
changed in Postgres 11. proisagg
was replaced by prokind
, true stored procedures were added. You need to adapt. See:
- How to drop all of my functions in PostgreSQL?
Returns:
stmt
---------------------------------------------------
DROP FUNCTION public.dblink(text);
DROP FUNCTION public.dblink(text, boolean);
DROP FUNCTION public.dblink(text, text);
DROP FUNCTION public.dblink(text, text, boolean);
Found four matches in the example because dblink uses overloaded functions.
Run DROP
statements selectively!
Alternatively, you can use the convenient cast to the object identifier type regprocedure
which returns a complete function signature including argument types:
-- SET LOCAL search_path=""; -- optional, to get all names schema-qualified
SELECT format('DROP %s %s;'
, CASE WHEN proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
, oid::regprocedure
) AS stmt
FROM pg_catalog.pg_proc
WHERE proname="dblink" -- function name
ORDER BY 1;