PostgreSQL: Drop a function without parameters

Question

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?

Super Kai - Kazuya Ito · Accepted Answer · 2023-12-07 19:17:23Z

122

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

edited Dec 7, 2023 at 19:17

Super Kai - Kazuya Ito

42.8k23 gold badges259 silver badges258 bronze badges

answered Jun 11, 2015 at 14:04

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Super Kai - Kazuya Ito · Accepted Answer · 2023-12-07 19:17:39Z

59

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.

edited Dec 7, 2023 at 19:17

Super Kai - Kazuya Ito

42.8k23 gold badges259 silver badges258 bronze badges

answered Jan 2, 2018 at 21:36

Mark McKelvy

3,5663 gold badges25 silver badges27 bronze badges

Comments

Super Kai - Kazuya Ito · Accepted Answer · 2023-12-07 18:49:13Z

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

Collectives™ on Stack Overflow

PostgreSQL: Drop a function without parameters

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related