Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
4. Returning values from a function
Syntax of PL/pgSQL
Function parameters
Handling exceptions
Summary

Instruction

Great! Now you know the tables and all the attributes about employees, their salaries, and departments.

The simplest kinds of user-defined functions are scalar functions. Scalar functions return a single value (not a set or a table), and they can take multiple arguments. The example below shows how to create a new function that returns the total number of employees:

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

In the function definition, you must declare what type of data the function returns. After the function name, you put the RETURNS keyword, followed by the name of the data type it returns. Here, the get_count_of_employees() function returns an integer.

To return a single value from a function, you have to use the RETURN keyword in the function's body. The RETURN keyword stops the execution of the function and returns the specified value.

You may return a single value, as we did in previous parts, or you can return the result of an SQL query (that returns one column and one row only). The SQL query should be surrounded with parentheses.

You may also return nothing from a function. In that case, you should use a return type of void:

CREATE FUNCTION do_nothing()
RETURNS void AS $$
BEGIN
END;
$$ LANGUAGE plpgsql;

Exercise

Create a function called get_count_of_departments() that takes no arguments and just returns the total number of departments within the organization. The required return data type is integer.