Product Documentation

Appearance

All platforms

SQL UDF Function Overloading ​

Function overloading allows you to create multiple functions with the same name. The input arguments to the functions can vary in number and can be of different data types. OUT parameters are excluded from the function signature during overloading. Therefore, functions cannot be overloaded based solely on differing return types when they have the same number and types of arguments. The following declaration is valid:

int calculate_value(int, int) 
int calculate_value(varchar)

However the following declaration returns an error:

int calculate_value(int, int) 
varchar calculate_value(int, int)
-- ERROR: function "calculate_value" already exists with same argument types

The following declarations conflict:

CREATE FUNCTION calculate_price(int) ...
CREATE FUNCTION calculate_price(int, out text) ...

Functions with different argument type lists will not be considered to conflict at creation time. However, if defaults are provided, they might conflict in use. For example:

CREATE FUNCTION calculate_price(int) ...
CREATE FUNCTION calculate_price(int, int default 42) ...

A call to calculate_price(10) will fail due to ambiguity about which function should be called.

Function Overloading Criteria ​

Yellowbrick resolves which function to execute based on the following criteria:

  1. Number of Parameters: Functions with the same name can differ by the number of input parameters.
  2. Data Types of Parameters: Functions can have the same name but operate on different data types (For example, integers, strings, arrays).
  3. Parameter Modes: IN parameters can distinguish overloaded functions.

SQL UDF Function Overloading Examples ​

Overloading by Number of Parameters

sql
-- Function with one parameter
CREATE OR REPLACE FUNCTION calculate_area(radius NUMERIC) RETURNS NUMERIC AS
$$ SELECT 3.14159 * radius * radius $$ LANGUAGE SQL;

-- Overloaded function with two parameters
CREATE OR REPLACE FUNCTION calculate_area(length NUMERIC, width NUMERIC) RETURNS NUMERIC AS
$$ SELECT length * width $$ LANGUAGE SQL;

In this example, calculate_area() is overloaded to handle calculations for both a circle (one parameter) and a rectangle (two parameters).

Overloading by Data Type

sql
-- Function takes an integer
CREATE OR REPLACE FUNCTION check_value(val INT) RETURNS NUMERIC AS 
$$ select val::numeric(16, 1); $$ LANGUAGE SQL;

-- Function takes a float
CREATE OR REPLACE FUNCTION check_value(val FLOAT) RETURNS NUMERIC AS 
$$ select val::numeric(16, 1); $$ LANGUAGE SQL;

-- Function takes a numeric
CREATE OR REPLACE FUNCTION check_value(val NUMERIC) RETURNS NUMERIC AS 
$$ select val::numeric(16, 1); $$ LANGUAGE SQL;

In this example, check_value() is overloaded to accept an integer, float or a numeric value.

sql
select check_value(4::int);
 check_value
--------------
 4.0

select check_value(4.3::float);
 check_value
--------------
 4.3

select check_value(4.3::numeric(2, 1));
 check_value
--------------
 4.3