# Overloaded Functions and Stored Procedures > **πŸ“ Note**: This is a Preview feature. [User Defined Functions (UDFs)](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-function-udf.md) and [Stored Procedures (SPs)](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-procedure.md) can be overloaded. Multiple functions (or stored procedures) that have the same name, but with different input arguments, can be created. Different numbers of input arguments and different input types are both supported. ## 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. ```sql SET GLOBAL enable_overloaded_functions = ON; ``` > **❗ Important**: Once `enable_overloaded_functions` is set to `ON`, it cannot be turned `OFF`. ## 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. ## Without Overloading ```sql 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) ... ``` ## With Overloading ```sql 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)](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-function-udf.md) or [CREATE PROCEDURE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-procedure.md) syntax to create an overloaded function or procedure. > **πŸ“ Note**: Function overloading works the same for functions and procedures. Throughout the rest of this topic, the word "function" refers to 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](https://docs.singlestore.com/#section-id235451811125364.md) 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](https://docs.singlestore.com/#section-id235451799178587.md) 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](https://docs.singlestore.com/#section-id235451799327216.md) 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. > **❗ Important**: When using `CREATE OR REPLACE FUNCTION`, if a non-overloaded function with the same name exists, that function is replaced with the new definition. ## Non-Overloaded Functions Functions created prior to enabling function overloading are non-overloaded functions and cannot be overloaded. A non-overloaded function may also be created if a function does not meet the criteria for function overloading, for example, if one of the input types is not supported for function overloading. Attempting to overload a non-overloaded function returns an error. To overload a non-overloaded function, either drop and recreate that function or use `CREATE OR REPLACE` to recreate the function. ## Variant ID Each overloaded function is assigned a unique variant ID which distinguishes that function from other functions with the same name. The variant IDs are incrementally assigned to overloaded functions starting with ID = 0. The variant ID does not have a meaning beyond being a unique identifier for a variant. Use `SHOW FUNCTIONS` to obtain an overloaded function's variant ID and its input argument types, as shown in the following example. ```sql 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; ``` ```output +-----------------+-----------+-----------------------+------------+---------+-------------+--------------+------+---------+ | 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 | | | +-----------------+-----------+-----------------------+------------+---------+-------------+--------------+------+---------+ ``` ## Type Equality Function overloading treats the following types as equal: * Types that differ only in the length parameter (e.g., `CHAR(10)` and `CHAR(20)`). * Types that are equivalent in SingleStore (e.g., `BOOL` and `TINYINT`). If two function variants have the same number of arguments and all argument types are equivalent, the second `CREATE FUNCTION` returns an error. The following commands attempt to create two variants of `PrettyPrint` that differ only in the length of the `CHAR` argument. The first function creation succeeds, but the second returns an error. ```sql CREATE FUNCTION PrettyPrint (value CHAR(10)) RETURNS VARCHAR(100) ... -- succeeds ``` ```sql CREATE FUNCTION PrettyPrint (value1 CHAR(20)) RETURNS VARCHAR(100) ... ``` ```output ERROR 3020 (HY000): Function `PrettyPrint` with same parameter types already exists. ``` ## Type Groups For function overloading, types within the following groups are considered equivalent. * `TINYINT` and `SMALLINT`.Β  * `MEDIUMINT` and `INT`. * Fixed-size string: `CHAR(LENGTH)` and `BINARY(LENGTH)` (differ only in charset). * Variable-length string: `VARCHAR` and `VARBINARY` (differ only in charset). * Blob and text types: `LONGBLOB`, `MEDIUMBLOB`, `BLOB`, `TINYBLOB`, `TEXT`, `LONGTEXT`, `MEDIUMTEXT`, and `TINYTEXT` (differ only in length). Any two types from any of these groups are considered equivalent. For example, `VARCHAR` and `VARBINARY` are considered equivalent, `LONGBLOB` and `TEXT` are considered equivalent. For a function variant to be valid, it must differ from existing variants in at least one of the following ways: * A different number of arguments. * At least one argument of a non-equivalent type. The following commands attempt to create two variants of `PrettyPrint`, both with fixed-size string arguments. The first function creation succeeds, but the second returns an error. ```sql CREATE FUNCTION PrettyPrint (value CHAR(10)) RETURNS VARCHAR(100) ... -- succeeds, creates function PrettyPrint with ID 0 ``` ```sql CREATE FUNCTION PrettyPrint (value1 BINARY(10)) RETURNS VARCHAR(100) ... ``` ```output 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'." ``` To resolve this error, either keep and use the first function definition, with the `CHAR(10)` argument, or drop that function and recreate the function with the `BINARY(10)` argument. ## Drop an Overloaded Function or Procedure > **πŸ“ Note**: Function overloading works the same for functions and procedures. Throughout the rest of this topic, the word "function" refers to 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/drop-function.md) syntax. To drop a function with multiple variants, obtain the variant ID from `SHOW FUNCTIONS` and use the following syntax. ```sql 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 > **πŸ“ Note**: Function overloading works the same for functions and procedures. Throughout the rest of this topic, the word "function" refers to function or procedure. 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](https://docs.singlestore.com/#section-id235451829624801.md) 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 ## Example 1 - Create and Call an Overloaded Function Create an overloaded function `print_type` with two variants. ```sql 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 ; ``` Display the overloaded functions and their variant IDs. ```sql SHOW FUNCTIONS; ``` ```output +-------------------+-----------+-----------------------+------------+---------+-------------+--------------+------+---------+ | 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 a table and insert data into that table. ```sql CREATE TABLE t(i INT, b BIGINT, t TINYINT); INSERT INTO t VALUES (1,2,3); ``` It is possible to call function `print_type` for columns `i` and `b` without explicit casts, because function resolution can unambiguously choose the best candidate. ```sql SELECT print_type(i) FROM t; ``` ```output +---------------+ | print_type(i) | +---------------+ | Integer | +---------------+ ``` ```sql SELECT print_type(b) FROM t; ``` ```output +---------------+ | print_type(b) | +---------------+ | Big Integer | +---------------+ ``` ## Example 2 - Use Explicit Casts to Resolve Ambiguous Function Resolution This example uses the table `t` created in the preceding example. ```sql CREATE TABLE t(i INT, b BIGINT, t TINYINT); INSERT INTO t VALUES (1,2,3); ``` Create two variants of the function `format_value`. One takes an `INT` as input and the other takes a `BIGINT` as input. ```sql 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 ; ``` As in the preceding example, when `format_value` is called on the `INT` column `i` or the `BIGINT` column `b`, the engine can unambiguously choose which variant to call. ```sql SELECT format_value(i) FROM t; ``` ```output +-----------------+ | format_value(i) | +-----------------+ | 1.0 | +-----------------+ ``` However, if `format_value` is called on the `TINYINT` column `t`, the engine cannot determine which variant to call, as `TINYINT` is a Compatible match for both `INT` and `BIGINT`. ```sql SELECT format_value(t) FROM t; ``` ```output 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. ``` Use an explicit cast to call `format_value` on the `TINYINT` column `t`. ```sql SELECT format_value(t:>INT) FROM t; ``` ```output +----------------------+ | format_value(t:>INT) | +----------------------+ | 3.0 | +----------------------+ ``` ## Example 3 - Overloading a Non-Overloaded Function (Error) Attempting to overload a non-overloaded function returns an error, as shown in the following example. To overload a non-overloaded function, drop and recreate it. Verify that function overloading is not enabled. ```sql SELECT enable_overloaded_functions; ``` ```output +-------------------------------+ | @@enable_overloaded_functions | +-------------------------------+ | 0 | +-------------------------------+ ``` Create a function `f`; `f` is created as a non-overloaded function. ```sql DELIMITER // CREATE FUNCTION f(a INT) RETURNS TEXT AS BEGIN RETURN "This is a non-overloaded function"; END // DELIMITER ; ``` Turn on function overloading. ```sql SET GLOBAL enable_overloaded_functions = ON; ``` ```output +-------------------------------+ | @@enable_overloaded_functions | +-------------------------------+ | 1 | +-------------------------------+ ``` Try to overload function `f`. An error is returned. ```sql DELIMITER // CREATE FUNCTION f(a BIGINT) RETURNS TEXT AS BEGIN RETURN "Try to overload function f"; END // DELIMITER ; ``` ```output 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. ``` To resolve the error drop `f`, and recreate it. ## Example 4 - Create and Call an Overloaded Procedure Create an overloaded procedure `output_type` with two variants. ```sql 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 ; ``` Display the overloaded procedures and their variant IDs. ```sql SHOW PROCEDURES; ``` ```output +--------------------+-----------+------------------+------------+---------+ | Procedures_in_test | Arguments | Routine Lifetime | Variant ID | Definer | +--------------------+-----------+------------------+------------+---------+ | output_type | BIGINT | Permanent | 1 | root@% | | output_type | INT | Permanent | 0 | root@% | +--------------------+-----------+------------------+------------+---------+ ``` Call the two variants. ```sql CALL output_type(1:>INT); ``` ```output +---------+ | Integer | +---------+ | Integer | +---------+ ``` ```sql CALL output_type(1:>BIGINT); ``` ```output +-------------+ | Big Integer | +-------------+ | Big Integer | +-------------+ ``` *** Modified at: March 17, 2026 Source: [/db/v9.1/reference/sql-reference/procedural-sql-reference/overloaded-functions-and-stored-procedures/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/overloaded-functions-and-stored-procedures/) (An index of the documentation is available at /llms.txt)