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.
