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

Instruction

Good job! Another way you can assign a value to a variable in a function is from an SQL query:

total := (SELECT count(*) FROM person);

We start with the name of the variable, then put the assignment operator :=, and finally, in parentheses, write the SQL query.

The result of the SQL query is assigned to the variable. Here, the query should return a single column and a single row.

Take a look at the example:

CREATE FUNCTION get_department_percent(dep_id integer)
RETURNS numeric AS $$
DECLARE
  total numeric;
  dep_emps numeric;
BEGIN
  total := (
    SELECT COUNT(*)
    FROM person
  );

  dep_emps := (
    SELECT COUNT(*)
    FROM person
    WHERE department_id = dep_id
  );

  RETURN (dep_emps / total) * 100;
END;
$$ LANGUAGE plpgsql;

Exercise

Write a function named female_percent() that computes the percentage of female employees in a given department.
The function should take one argument: dep_id of type integer. Return the value rounded to 2 decimal digits (the result should be of a numeric type).

Remember that you can always invoke your function to see what's wrong – the database will save your function but will throw some errors when you try to run it.