Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Writing User-Defined Functions in PostgreSQL

Introduction
Syntax of PL/pgSQL
Function parameters
22. Function overloading
Handling exceptions
Summary

Instruction

Postgres allows us to define more than one function with the same name, as long as the arguments are different (different types, order, or number). If we have two or more functions with the same name, we say those functions are overloaded.

Here, we have two functions with the same name but different arguments:

CREATE FUNCTION get_count_of_employees()
RETURNS integer AS $$
BEGIN
  RETURN (SELECT COUNT(1) FROM person);
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION get_count_of_employees(min_joined_year IN integer)
RETURNS integer AS $$
BEGIN
  RETURN (SELECT COUNT(1) FROM person WHERE extract(year FROM start_date) >= min_joined_year);
END;
$$ LANGUAGE plpgsql;

Now, we can say that the get_count_of_employees() function is overloaded.

If we invoke the get_count_of_employees() function, we get the total number of employees. If we invoke get_count_of_employees(2015), we get the total number of employees who joined the company after 2015.

Exercise

Amend the existing get_count_of_earners(min_salary_val IN decimal) function.

The new function has to have the same name, get_count_of_earners(), but will have two arguments: min_salary_val and max_salary_val, both of type decimal.

The function will return the number of earners who have a salary greater than or equal to min_salary_val and less than or equal to max_salary_val.