PL/PGSQL 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.

Input:

CREATE OR REPLACE FUNCTION get_rental_duration(p_customer_id INTEGER)

RETURNS INTEGER AS $$

DECLARE

rental_duration INTEGER;

BEGIN

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;

Output:

SELECT get_rental_duration(232);

Input:

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;

Output:

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