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
17. OUT parameters
Handling exceptions
Summary

Instruction

Great job! Now, we're going to focus on other types of function parameters.

Let's start with OUT parameters. The OUT parameters allow us to return a value from a function as part of its result set. To define an OUT parameter, we use the OUT keyword:

CREATE FUNCTION get_count_products_in_stock(IN reg_id integer, OUT count_of_products integer)
AS $$
BEGIN
  count_of_products := (SELECT COUNT(*) FROM prod_stock WHERE region_id = reg_id);
END;
$$ LANGUAGE plpgsql;

Because we specified the OUT parameters, the RETURNS and RETURN statements are optional in the function definition.

Exercise

Create a new function that returns the number of employees and their average salary (as OUT parameters) for a given department (dep_id as an IN parameter).

The function should be named get_employee_statistics() and have three arguments:

  • IN dep_id of type integer.
  • OUT count_of_employees of type integer.
  • OUT average_salary of type decimal rounded to 2 decimal digits.