Summer Deals - 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! Of course, you can combine conditions in an IF statement with boolean operators: AND, OR, and NOT. You can also use all comparison operators you know from SQL: <, >, <=, >=, =, etc.

The number of personal days for most employees is 20. However, employees from IT departments (department IDs: 2, 3, and 4) hired before 2016 get 30 personal days:

CREATE FUNCTION get_personal_days(emp_id integer)
RETURNS integer AS $$
DECLARE
  start_year integer;
  dep_id integer;
BEGIN
  start_year := (SELECT extract(year FROM start_date) FROM person WHERE id = emp_id);
  dep_id := (SELECT department_id FROM person WHERE id = emp_id);
  IF dep_id IN (2, 3, 4) AND start_year < 2016 THEN
    RETURN 30;
  ELSE
    RETURN 20;
  END IF;
END;
$$ LANGUAGE plpgsql;

Exercise

Write a function named get_bonus(emp_id integer) which computes the bonus for an employee and returns it as a decimal.

For employees in IT departments (departments IDs: 2, 3, and 4), the bonus is:

  1. 1500.00 for employees earning less than 50000.
  2. 2500.00 for employees earning 50000 or more.

For other departments, the bonus is:

  1. 1000.00 for employees earning less than 80000.
  2. 2000.00 for employees earning 80000 or more.