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

by

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:

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;

More Related Contents:

  1. How to convert primary key from integer to serial?
  2. Can I make a plpgsql function return an integer without using a variable?
  3. Why can I create a table with PRIMARY KEY on a nullable column?
  4. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  5. What to do with null values when modeling and normalizing?
  6. Postgres: INSERT if does not exist already
  7. postgres default timezone
  8. How to change PostgreSQL user password?
  9. How to get the count of current month Sunday’s in psql?
  10. quick random row selection in Postgres
  11. Safely rename tables using serial primary key columns
  12. Restore a postgres backup file using the command line?
  13. Postgres manually alter sequence
  14. How to close idle connections in PostgreSQL automatically?
  15. Refresh a materialized view automatically using a rule or notify
  16. postgresql – replace all instances of a string within text field
  17. psql: FATAL: database “” does not exist
  18. Postgres FOR LOOP
  19. Storing Images in PostgreSQL
  20. Psql could not connect to server: No such file or directory, 5432 error?
  21. Strange behaviour in Postgresql
  22. Postgres: Distinct but only for one column
  23. In PostgreSQL, how to insert data with COPY command?
  24. No function matches the given name and argument types
  25. Cursor based records in PostgreSQL
  26. Maximum characters in labels (table names, columns etc)
  27. Configuration parameter work_mem in PostgreSQL on Linux
  28. How to work with PGpoint for Geolocation using PostgreSQL?
  29. Spring Docker container cannot access Postgres Docker container
  30. PostgreSQL composite primary key

Leave a Comment