74

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;
Ben
  • 20,737
  • 12
  • 71
  • 115
Joseph Idziorek
  • 4,853
  • 6
  • 23
  • 37

2 Answers2

117

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);
klin
  • 112,967
  • 15
  • 204
  • 232
58

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.

Mark McKelvy
  • 3,328
  • 2
  • 19
  • 24