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
Handling exceptions
Summary

Instruction

Great! PL/pgSQL offers another syntax for assigning the value of an SQL query to a variable. You can use:

SELECT count(*) INTO total FROM person;

Here, you write a regular SQL query, but after the list of columns, you put INTO followed by the name of your variable. The query is executed and its result is assigned to the variable.

The company must pay a monthly social contribution of 1000 for each employee. This function computes the total yearly cost of an employee with a given id:

CREATE FUNCTION get_employee_cost(emp_id integer)
RETURNS decimal AS $$
DECLARE
  salary_value decimal;
BEGIN
  SELECT value INTO salary_value FROM salary WHERE person_id = emp_id;
  RETURN salary_value + 1000 * 12;
END;
$$ LANGUAGE plpgsql;

First, the function selects the salary of the employee and copies it to the salary_value variable. Next, it returns the yearly cost of the social contribution for this employee.

Exercise

Write a function named get_average_employee_cost() that computes the average employee cost in a department. The function should take one dep_id argument of type integer. The function should:

  • Compute the total salary of employees in the given department (by using a proper SQL query).
  • Find out the total number of employees in the given department.
  • Compute and return (as a decimal rounded to 2 decimal places) the average yearly cost of employees in the department.

Stuck? Here's a hint!

Here's the piece of code to count the sum of salaries in a given department:

SELECT sum(s.value) INTO total_salary
FROM person p
LEFT JOIN salary s
  ON p.person_id = s.person_id
WHERE department_id = dep_id;