Spring Deals - hours only!Up to 70% off on all courses and bundles.-Close
Syntax of PL/pgSQL
Function parameters
20. INOUT parameters
Handling exceptions


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 $$
  users_run_total := users_run_total + (
    FROM person p
    WHERE p.department_id = dep_id
$$ LANGUAGE plpgsql;


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 year of type integer.
  • INOUT users_run_total of type integer.

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)