Overloaded Functions and Stored Procedures

Enable Function and Procedure Overloading

Enable function and procedure overloading by setting the engine variable enable_overloaded_functions to ON. This variable is set to OFF by default.

SET GLOBAL enable_overloaded_functions = ON;

Introductory Example

Imagine creating a "pretty print" function for different types; the "pretty print" functions receive values and return a formatted string as a VARCHAR.

Without function overloading, each variant must have a unique name. With function overloading, it is possible to define multiple functions with the same name and let the system choose the correct function based on argument types.

CREATE OR REPLACE FUNCTION PrettyPrint_Int (value INT) RETURNS VARCHAR(100) ...
CREATE OR REPLACE FUNCTION PrettyPrint_2Ints (value1 INT, value2 INT) RETURNS VARCHAR(100) ...
CREATE OR REPLACE FUNCTION PrettyPrint_Varchar (value VARCHAR(100)) RETURNS VARCHAR(100) ...

CREATE OR REPLACE FUNCTION PrettyPrint (value INT) RETURNS VARCHAR(100) ...
CREATE OR REPLACE FUNCTION PrettyPrint (value1 INT, value2 INT) RETURNS VARCHAR(100) ...
CREATE OR REPLACE FUNCTION PrettyPrint (value VARCHAR(100)) RETURNS VARCHAR(100) ...

Supported Types

The following types are supported for function overloading:

• Numeric types

  • INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT

  • BOOL

  • FLOAT, DOUBLE, DECIMAL

• String types

  • CHAR, VARCHAR

  • BINARY, VARBINARY

  • TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT

  • BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB

Create an Overloaded Function or Procedure

Use the standard CREATE FUNCTION (UDF) or CREATE PROCEDURE syntax to create an overloaded function or procedure.

When function overloading is enabled and CREATE FUNCTION is called, the system creates an overloaded function if the following criteria are met.

• All input types are in the list of supported types.

• There is not a non-overloaded function with the same name as the new function.

  • Refer to Non-Overloaded Functions for more information.

• There is no function, overloaded or not, with the same name and input types as the new function.

  • Overloading on type length is not supported. Refer to Type Equality for more information.

  • Overloading with types that are within certain groups of types is not supported (for example, types that differ only in length or charset). Refer to Type Groups for more information.

If these function overloading criteria are not met, the following occurs:

• If there is a non-overloaded function with the same name as the new function, an error is raised.

• If there is a function, overloaded or not, with the same name and input types as the new function, an error is raised.

• If one or more input type(s) are not in the list of supported types, a non-overloaded function is created.

When CREATE OR REPLACE FUNCTION is used:

• If a non-overloaded function with the same name exists, that function is replaced with the new definition.

• If an overloaded function with the same signature (name and arguments) exists, that function is replaced with the new definition.

• Otherwise, a new function is created.

CREATE OR REPLACE FUNCTION x() ...
CREATE OR REPLACE FUNCTION x(a int) ...
CREATE OR REPLACE FUNCTION x(a int, b int) ...
CREATE OR REPLACE FUNCTION x(a bigint) ...
CREATE OR REPLACE FUNCTION y(a bigint) ...
SHOW FUNCTIONS;

+-----------------+-----------+-----------------------+------------+---------+-------------+--------------+------+---------+
| Functions_in_db | Arguments | Function Type         | Variant ID | Definer | Data Format | Runtime Type | Link | Options |
+-----------------+-----------+-----------------------+------------+---------+-------------+--------------+------+---------+
| x               |           | User Defined Function |          0 | root@%  |             | PSQL         |      |         |
| x               | BIGINT    | User Defined Function |          3 | root@%  |             | PSQL         |      |         |
| x               | INT       | User Defined Function |          1 | root@%  |             | PSQL         |      |         |
| x               | INT, INT  | User Defined Function |          2 | root@%  |             | PSQL         |      |         |
| y               | BIGINT    | User Defined Function |          0 | root@%  |             | PSQL         |      |         |
+-----------------+-----------+-----------------------+------------+---------+-------------+--------------+------+---------+

CREATE FUNCTION PrettyPrint (value CHAR(10)) RETURNS VARCHAR(100) ... -- succeeds

CREATE FUNCTION PrettyPrint (value1 CHAR(20)) RETURNS VARCHAR(100) ...

ERROR 3020 (HY000): Function `PrettyPrint` with same parameter types already exists.

CREATE FUNCTION PrettyPrint (value CHAR(10)) RETURNS VARCHAR(100) ...
-- succeeds, creates function PrettyPrint with ID 0

CREATE FUNCTION PrettyPrint (value1 BINARY(10)) RETURNS VARCHAR(100) ...

ERROR 3021 (HY000): Cannot create function `db`.`PrettyPrint`: it is ambiguous with respect to a variant with ID 5. Creating such a function will result in ambiguous function calls. If you want to create function with the new definition, drop the existing function with 'DROP FUNCTION `db`.`PrettyPrint` WITH ID 5'."

Drop an Overloaded Function or Procedure

If there is only one variant of the overloaded function, that is, there is only one function with that function name, drop the function using the existing DROP FUNCTION syntax.

To drop a function with multiple variants, obtain the variant ID from SHOW FUNCTIONS and use the following syntax.

DROP FUNCTION [IF EXISTS] { function_name | database_name.function_name } WITH ID variant_id;

Dropping an overloaded function with multiple variants without the WITH ID clause returns an error.

Overloaded Function and Procedure Resolution

When an overloaded function with multiple variants is called, the engine resolves which variant to run by finding the variant whose parameter types most closely match the types of the input arguments.

The engine defines argument type matches as follows:

• Exact (strong) match: The argument type exactly matches the parameter type (for example, INT - INT).

• Compatible (light) match: The argument can be converted to the parameter type (for example, INT - BIGINT).

  • If the parameter and argument types are both numeric types or both string types, that is considered a Compatible match.

• No match:  All other conversions are not considered a match (for example, VARCHAR - INT).

The engine chooses the variant to run as follows:

1. Filter by number of arguments.

   • Exclude all variants with a different number of arguments than the number of parameters in the function call.

   • If there is exactly one variant remaining, select that variant. If there are no variants remaining, return an error. If there are multiple variants remaining, proceed to Step 2.

2. Filter by argument type.

   • Exclude all variants where at least one parameter in the function call cannot be matched (exact or compatible) to an argument in the variant definition.

   • If there is exactly one variant remaining, select that variant. If there are no variants remaining, return an error. If there are multiple variants remaining, proceed to Step 3.

3. Prefer the most specific match.

   • Among the remaining candidates, select the variant with the highest number of exact (strong) matches.

4. If multiple variants have the same number of exact (strong) matches, the function call is considered ambiguous and an error is returned.

   • Use explicit casts in the function call to avoid the ambiguity. Refer to Example 2 - Use Explicit Casts to Resolve Ambiguous Function Resolution for an example of using explicit casts to avoid ambiguous function calls.

After the variant is selected, the engine typecasts the arguments in the function call to the corresponding types in the variant definition. If an argument cannot be implicitly cast to the required type, an error is returned.

Usage of Overloaded Functions in UDAFs

Overloaded functions cannot be used in User-Defined Aggregate Functions (UDAFs). This means:

• Overloading a function that is already used in a UDAF is not supported.

• Creating a UDAF using an overloaded function is not supported.

Examples

DELIMITER //

CREATE FUNCTION print_type(input INT) RETURNS TEXT AS
  BEGIN
    RETURN "Integer";
  END //

CREATE FUNCTION print_type(input BIGINT) RETURNS TEXT AS
BEGIN
RETURN "Big Integer";
END//

DELIMITER ;

SHOW FUNCTIONS;

+-------------------+-----------+-----------------------+------------+---------+-------------+--------------+------+---------+
| Functions_in_test | Arguments | Function Type         | Variant ID | Definer | Data Format | Runtime Type | Link | Options |
+-------------------+-----------+-----------------------+------------+---------+-------------+--------------+------+---------+
| print_type        | BIGINT    | User Defined Function |          1 | root@%  |             | PSQL         |      |         |
| print_type        | INT       | User Defined Function |          0 | root@%  |             | PSQL         |      |         |
+-------------------+-----------+-----------------------+------------+---------+-------------+--------------+------+---------+

CREATE TABLE t(i INT, b BIGINT, t TINYINT);

INSERT INTO t VALUES (1,2,3);

SELECT print_type(i)
FROM t;

+---------------+
| print_type(i) |
+---------------+
| Integer       |
+---------------+

SELECT print_type(b)
FROM t;

+---------------+
| print_type(b) |
+---------------+
| Big Integer   |
+---------------+

CREATE TABLE t(i INT, b BIGINT, t TINYINT);

INSERT INTO t VALUES (1,2,3);

DELIMITER //

CREATE FUNCTION format_value(input INT) RETURNS TEXT AS
  BEGIN
    RETURN FORMAT(input, 1);
  END //

CREATE FUNCTION format_value(input BIGINT) RETURNS TEXT AS
BEGIN
RETURN FORMAT(input, 1);
END//

DELIMITER ;

SELECT format_value(i)
FROM t;

+-----------------+
| format_value(i) |
+-----------------+
| 1.0             |
+-----------------+

SELECT format_value(t)
FROM t;

ERROR 3021 (HY000): Ambiguous function call: `format_value` has multiple overloads matching the provided arguments. Consider using explicit casts for one or more arguments to specify parameter types.

SELECT format_value(t:>INT)
FROM t;

+----------------------+
| format_value(t:>INT) |
+----------------------+
| 3.0                  |
+----------------------+

SELECT enable_overloaded_functions;

+-------------------------------+
| @@enable_overloaded_functions |
+-------------------------------+
|                             0 |
+-------------------------------+

DELIMITER //

CREATE FUNCTION f(a INT) RETURNS TEXT AS
  BEGIN
    RETURN "This is a non-overloaded function";
  END //

DELIMITER ;

SET GLOBAL enable_overloaded_functions = ON;

+-------------------------------+
| @@enable_overloaded_functions |
+-------------------------------+
|                             1 |
+-------------------------------+

DELIMITER //

CREATE FUNCTION f(a BIGINT) RETURNS TEXT AS
  BEGIN
    RETURN "Try to overload function f";
  END //

DELIMITER ;

ERROR 3012 (HY000): Cannot create function `f` because it already exists. To overload this function, it must be upgraded first: drop the function and then recreate it.

DELIMITER //

CREATE PROCEDURE output_type(input INT) AS
  BEGIN
    ECHO SELECT "Integer";
  END //

CREATE PROCEDURE output_type(input BIGINT) AS
  BEGIN
    ECHO SELECT "Big Integer";
  END//

DELIMITER ;

SHOW PROCEDURES;

+--------------------+-----------+------------------+------------+---------+
| Procedures_in_test | Arguments | Routine Lifetime | Variant ID | Definer |
+--------------------+-----------+------------------+------------+---------+
| output_type        | BIGINT    | Permanent        |          1 | root@%  |
| output_type        | INT       | Permanent        |          0 | root@%  |
+--------------------+-----------+------------------+------------+---------+

CALL output_type(1:>INT);

+---------+
| Integer |
+---------+
| Integer |
+---------+

CALL output_type(1:>BIGINT);

+-------------+
| Big Integer |
+-------------+
| Big Integer |
+-------------+