Is there a way to disable function overloading in Postgres

Question

My users and I do not use function overloading in PL/pgSQL. We always have one function per (schema, name) tuple. As such, we'd like to drop a function by name only, change its signature without having to drop it first, etc. Consider for example, the following function:

CREATE OR REPLACE FUNCTION myfunc(day_number SMALLINT)
RETURNS TABLE(a INT)
AS
$BODY$
BEGIN
  RETURN QUERY (SELECT 1 AS a);
END;
$BODY$
LANGUAGE plpgsql;

To save time, we would like to invoke it as follows, without qualifying 1 with ::SMALLINT, because there is only one function named myfunc, and it has exactly one parameter named day_number:

SELECT * FROM myfunc(day_number := 1)

There is no ambiguity, and the value 1 is consistent with SMALLINT type, yet PostgreSQL complains:

SELECT * FROM myfunc(day_number := 1);

ERROR:  function myfunc(day_number := integer) does not exist
LINE 12: SELECT * FROM myfunc(day_number := 1);
                       ^
HINT:  No function matches the given name and argument types.
You might need to add explicit type casts.

When we invoke such functions from Python, we use a wrapper that looks up functions' signatures and qualifies parameters with types. This approach works, but there seems to be a potential for improvement.

Is there a way to turn off function overloading altogether?

PostgreSQL uses strong typing - and it should be respected. When your function is defined with smallint, it cannot be called with int. If you don't would to solve types - use a most generic types in function parameters. In this case integer instead smallint. — Pavel Stehule

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2015-02-25T02:46:12

This is actually not directly a matter of function overloading (which would be impossible to "turn off"). It's a matter of function type resolution. (Of course, that algorithm could be more permissive without overloaded functions.)

All of these would just work:

SELECT * FROM myfunc(day_number := '1');
SELECT * FROM myfunc('1');               -- note the quotes

SELECT * FROM myfunc(1::smallint);
SELECT * FROM myfunc('1'::smallint);

Why?

The last two are rather obvious, you mentioned that in your question already.
The first two are more interesting, the explanation is buried in the Function Type Resolution:

unknown literals are assumed to be convertible to anything for this purpose.

And that should be the simple solution for you: use string literals.

An untyped literal '1' (with quotes) or "string literal" as defined in the SQL standard is different in nature from a typed literal (or constant).

A numeric constant 1 (without quotes) is cast to a numeric type immediately. The manual:

A numeric constant that contains neither a decimal point nor an exponent is initially presumed to be type integer if its value fits in type integer (32 bits); otherwise it is presumed to be type bigint if its value fits in type bigint (64 bits); otherwise it is taken to be type numeric. Constants that contain decimal points and/or exponents are always initially presumed to be type numeric.

The initially assigned data type of a numeric constant is just a starting point for the type resolution algorithms. In most cases the constant will be automatically coerced to the most appropriate type depending on context. When necessary, you can force a numeric value to be interpreted as a specific data type by casting it.

Bold emphasis mine.

The assignment in the function call (day_number := 1) is a special case, the data type of day_number is unknown at this point. Postgres cannot derive a data type from this assignment and defaults to integer.

Consequently, Postgres looks for a function taking an integer first. Then for functions taking a type only an implicit cast away from integer, in other words:

SELECT casttarget::regtype
FROM   pg_cast
WHERE  castsource = 'int'::regtype
AND    castcontext = 'i';

All of these would be found - and conflict if there were more than one function. That would be function overloading, and you would get a different error message. With two candidate functions like this:

SELECT * FROM myfunc(1);

ERROR:  function myfunc(integer) is not unique

Note the "integer" in the message: the numeric constant has been cast to integer.

However, the cast from integer to smallint is "only" an assignment cast. And that's where the journey ends:

No function matches the given name and argument types.

SQL Fiddle.

More detailed explanation in these related answers:

Dirty fix

You could fix this by "upgrading" the cast from integer to smallint to an implicit cast:

UPDATE pg_cast
SET    castcontext = 'i'
WHERE  castsource = 'int'::regtype
AND    casttarget  = 'int2'::regtype;

But I would strongly discourage tampering with the default casting system. Only consider this if you know exactly what you are doing. You'll find related discussions in the Postgres lists. It can have all kinds of side effects, starting with function type resolution, but not ending there.

Aside

Function type resolution is completely independent from the used language. An SQL function would compete with PL/perl or PL/pgSQL or "internal" functions just the same. The function signature is essential. Built-in functions only come first, because pg_catalog comes first in the default search_path.

Is there a way to disable function overloading in Postgres

3 Answers

Why?

Dirty fix

Aside