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

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 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)