PostgreSQL: How to DROP FUNCTION IF EXISTS without specifying parameters?
PostgresqlPlpgsqlPostgresql Problem Overview
I can successfully create a function as follows:
CREATE FUNCTION Foo(MY_Value INT) RETURNS INT
AS 'SELECT 2 + MY_Value'
LANGUAGE SQL
However, if I first want to check if the function exists and then drop it if I does, I must specify the following:
DROP FUNCTION IF EXISTS Foo(My_Value INT);
Without specifying the input parameters, the following returns an error stating "NOTICE: function foo() does not exist, skipping"
DROP FUNCTION IF EXISTS Foo();
Similar to MySQL, is there a way to drop a FUNCTION in PostgreSQL without having to specify the parameters to the function? In other words, is there an equivalent for the following in MySQL statement (i.e., drop the stored procedure without specifying the input parameters)?
DROP PROCEDURE IF EXISTS Foo;
Postgresql Solutions
Solution 1 - Postgresql
In Postgres functions can be overloaded, so parameters are necessary to distinguish overloaded functions. To unambiguously identify a function you can put only types of its parameters.
DROP FUNCTION IF EXISTS Foo(INT);
Solution 2 - Postgresql
As of Postgres 10 you can drop functions by name only, as long as the names are unique to their schema.
Example:
drop function if exists Foo;
Documentation here.