PostgreSQL – Function Overloading

PostgreSQL allows more than one function to have the same name, so long as the arguments are different. If more than one function has the same name, we say those functions are overloaded. When a function is called, PostgreSQL determines the exact function is being called based on the input arguments.

Let’s take a look at the following get_rental_duration() function applied to the sample dvdrental database.

CREATE OR REPLACE FUNCTION get_rental_duration(p_customer_id INTEGER)
    RETURNS INTEGER AS $$
    
DECLARE 
    rental_duration INTEGER; 
BEGIN
    -- get the rate based on film_id
    SELECT INTO rental_duration SUM( EXTRACT( DAY FROM return_date - rental_date)) 
    FROM rental 
    WHERE customer_id=p_customer_id;

    RETURN rental_duration;
END; $$
LANGUAGE plpgsql;

The get_rental_function accepts p_customer_id as the argument. It returns the sum of duration (in days) that a specific customer rented DVDs. For example, we can get the rental duration of the customer with customer id 278, we call the get_rental_duration function as follows:

SELECT get_rental_duration(278);

It returns the following:

Suppose, we want to know the rental duration of a customer from a specific date up to now. We can add one more parameter p_from_date to the get_rental_duration() function, or we can develop a new function with the same name but have two parameters as follows:

CREATE OR REPLACE FUNCTION get_rental_duration(p_customer_id INTEGER, p_from_date DATE)
    RETURNS INTEGER AS $$
DECLARE 
    rental_duration integer;
BEGIN
    -- get the rental duration based on customer_id and rental date
    SELECT INTO rental_duration
                SUM( EXTRACT( DAY FROM return_date + '12:00:00' - rental_date)) 
    FROM rental 
    WHERE customer_id= p_customer_id AND 
          rental_date >= p_from_date;
     
    RETURN rental_duration;
END; $$
LANGUAGE plpgsql;

This function has the same name as the first one except that it has two parameters. We say the function is overloaded by the get_rental_duration(integer, date) function. The following statement gets the rental duration of the customer with customer id 278 since July 1st, 2005:

SELECT get_rental_duration(278, '2005-07-01');

It will result in the following: