postgresql - How to get function parameter lists (so I can drop a function)

Question

I want to get the SQL to drop a function in PostgreSQL. I write DROP FUNCTION and a get function name from pg_proc. That is not problem. However if I leave blank parameters it will not drop the function.

I checked the manual and there is written then I have to identify the function with its parameters to drop it, eg DROP FUNCTION some_func(text,integer) not just DROP FUNCTION some_func.

Where can I find the parameters? In the function's row on in the pg_proc table there is no parameters. So how can I get the SQL to drop the function?

Answer 1

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 within ALTER 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;