Summary: in this tutorial, we will introduce you to PL/pgSQL function overloading feature to help you develop flexible functions.
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 exact function is being called based on the input arguments.
Let’s take a look at the following get_rental_duration() function.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 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 customer with customer id 232, we call the get_rental_duration function as follows:
1 | SELECT get_rental_duration(232); |
It returns 90 days.
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_rom_date to the get_retal_duration() function or we can develop a new function with the same name but have two parameters as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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 - 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 get_rental_duration(integer) function is overloaded by the get_rental_duration(integer,date) function.
The following statement gets the rental duration of the customer with customer id 232 since July 1st 2005:
1 | SELECT get_rental_duration(232,'2005-07-01'); |
It returns 71days. Notice that if we ignore second argument, PostgreSQL will call the first function.
PL/pgSQL function overloading and default value
In the get_rental_duration(integer,date) function, if we want to add a default value for the second argument as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE OR REPLACE FUNCTION get_rental_duration( p_customer_id INTEGER, p_from_date DATE DEFAULT '2005-01-01' ) 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 - rental_date)) FROM rental WHERE customer_id= p_customer_id AND rental_date >= p_from_date; RETURN rental_duration; END; $$ LANGUAGE plpgsql; |
It means that if we ignore passing the p_from_date argument, PostgreSQL will use January 1st 2015 as the default value. Let’s try it.
1 | SELECT get_rental_duration(232); |
Oh, we got an error message:
1 2 3 4 | [Err] ERROR: function get_rental_duration(integer) is not unique LINE 1: SELECT get_rental_duration(232); ^ HINT: Could not choose a best candidate function. You might need to add explicit type casts. |
Should PostgreSQL call the first function with one parameter or the second function with two parameters? It could not choose which one to call.
So when you overload an existing function, you should always make that their function interfaces are different. Now, only the second function can be used by passing the second argument explicitly. The first one cannot be used because it confuses PostgreSQL about which function to call.
We need to remove the get_rental_duration() function with default value, and recreate the get_rental_duration function with two parameters again without specifying the default value for the second parameter.
1 | DROP FUNCTION get_rental_duration(INTEGER,DATE); |
Notice that you must specify the parameters together with the function name when you drop the function. In fact, in PostgreSQL, the complete name of function includes both name and parameters.
One final important note is that you should avoid using an excessive number of overloads that makes developers difficult to know which function will be called by looking at the code.
In this tutorial, you learned how to overload an existing function by adding more parameters to it. In addition, you learned about some pitfalls if you use default values for parameters in the overloaded function.