Good stuff! The next topic we are going to focus on is conditional statements. The PL/pgSQL language, like others, has an IF statement. The full form of an IF statement looks like this:
IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF;
The ELSIF and ELSE blocks are optional.
We can use this type of IF statement when we want to check several alternatives in turn. All IF conditions are tested successively until the first one that is true is found. If none of the IF conditions are true, then the ELSE block (if any) is executed.
See the example below showing the most complex type of IF statement, where the salary_value is being compared to the two thresholds – lower_threshold and upper_threshold:
CREATE FUNCTION compute_vacation_support(salary_value decimal)
RETURNS decimal AS $$
DECLARE
lower_threshold decimal := 100000.00;
upper_threshold decimal := 150000.00;
BEGIN
IF salary_value > upper_threshold THEN
RETURN 0.00;
ELSIF salary_value > lower_threshold THEN
RETURN 600.00;
ELSE
RETURN 1000.00;
END IF;
END;
$$ LANGUAGE plpgsql;
Write a function named get_training_funding(emp_id integer) that computes the training funding for an employee. The training funding for employees hired before 2014 is 2000.00; the funding for employees hired in 2014 is 6000.00 (this was one of the perks company used to hire new employees that year); and the funding for new employees is 1000.00.
Use a SELECT statement along with the extract() function to retrieve the year:
SELECT extract(year FROM start_date) INTO start_year
