Instruction
Well done! Keep it up. The next type of parameter we're going to cover is the INOUT parameter.
INOUT is basically a combination of IN and OUT parameters. A function takes the argument, modifies it on the fly, and returns it as part of the result.
The function below returns the total number of employees in a given department, summed with the number of users passed in as users_run_total (we may know this number from a source other than our database):
CREATE FUNCTION count_department_employees (IN dep_id integer, INOUT users_run_total integer) AS $$
BEGIN
users_run_total := users_run_total + (
SELECT COUNT(*)
FROM person p
WHERE p.department_id = dep_id
);
END;
$$ LANGUAGE plpgsql;
Exercise
Create a new function named count_of_employees() that calculates the total number of employees hired after a certain year and returns it as part of the result.
This function takes two arguments:
IN yearof typeinteger.INOUT users_run_totalof typeinteger.
The function logic is as follows: retrieve the number of employees who were hired after the year provided as the year argument.
Add this count to the users_run_total value.
Stuck? Here's a hint!
To check if a given year is correct, use the following piece of code:
EXTRACT(year FROM start_date) > year)
