CODEWITHSUNDEEP
sqldatabasepostgresqlplpgsqldrop
Joseph Idziorek
Joseph Idziorek

Reputation: 5071

PostgreSQL: Drop a function without parameters

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?

Upvotes: 78

Views: 78691

Answers (3)

Mark McKelvy
Mark McKelvy

Reputation: 3536

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.

Upvotes: 59

klin
klin

Reputation: 121574

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);

Upvotes: 122

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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);

Upvotes: 1

Related Questions