PostgreSQL: Drop a function without parameters

76.4k views Asked by At

I created a function as shown below:

CREATE FUNCTION my_func(value INTEGER) RETURNS VOID AS $$
BEGIN
END;
$$ LANGUAGE plpgsql;

But I need to specify the parameter to drop the function as shown below:

DROP FUNCTION my_func(value INTEGER);

Because if not specifying the parameter as shown below:

DROP FUNCTION my_func;

Then, I got the error below:

ERROR: function name "my_func" is not unique
HINT: Specify the argument list to select the function unambiguously.

So, can I drop a function without parameters?

3

There are 3 answers

0
klin On BEST ANSWER

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 my_func(INT);
0
Super Kai - Kazuya Ito On

You need to specify zero or more parameters with () if there are multiple same name functions. *The doc explains Function Overloading.

For example, you create 2 functions as shown below:

CREATE FUNCTION my_func() RETURNS VOID AS $$
BEGIN
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION my_func(value INTEGER) RETURNS VOID AS $$
BEGIN
END;
$$ LANGUAGE plpgsql;

Then, you can drop the 1st function with the SQL below:

DROP FUNCTION my_func();

Or, you can drop the 2nd function with the SQL below:

DROP FUNCTION my_func(INTEGER);

And, you create 2 procedures as shown below:

CREATE PROCEDURE my_proc() LANGUAGE plpgsql AS $$
BEGIN
END;
$$;
CREATE PROCEDURE my_proc(value INTEGER) LANGUAGE plpgsql AS $$
BEGIN
END;
$$;

Then, you can drop the 1st procedure with the SQL below:

DROP PROCEDURE my_proc();

Or, you can drop the 2nd procedure with the SQL below:

DROP PROCEDURE my_proc(INTEGER);
0
Mark McKelvy On

As of Postgres 10 you can drop functions by name only, as long as the names are unique to their schema.

Example:

drop function my_func;

Documentation here.