Snatch any course you want for just $15! Go to “Courses” and find a course ↓
Introduction
Syntax of PL/pgSQL
Function parameters
Handling exceptions
Summary

Instruction

Great! Next, we'll learn about variables in PL/pgSQL.

What is a variable? A variable stores a value that can be modified and used inside a block of code or function. Before we use a variable, we must declare it in the declaration section of a PL/pgSQL function. The declaration section starts with DECLARE keyword and ends with the BEGIN keyword of the function body block.

CREATE FUNCTION get_monthly_salary(salary decimal)
RETURNS decimal AS $$
DECLARE
  months integer := 12;
  monthly_salary decimal;
BEGIN
  monthly_salary := salary/months;
  RAISE NOTICE 'Monthly salary is %', monthly_salary;
  return round(monthly_salary, 4);
END;
$$ LANGUAGE plpgsql;

SELECT get_monthly_salary(1000);

In the example above, we declared two variables (after the DECLARE keyword): months and monthly_salary. Variables must have a specific data type associated with them. In our case, months is integer, and monthly_salary is decimal. When we declare a new variable, we put the type of the variable after its name.

The months variable has a value of 12 assigned. We use the := symbol, followed by the value.
The monthly_salary variable is declared in the DECLARE part of the function, but its value is not assigned – its initial value is NULL.
The assignment for the monthly_salary variable is done in the function body.

Exercise

Write a function named get_tax() that takes one argument: salary of type decimal. The function should return the tax for the given salary rounded to 2 decimal places (as a decimal type). The tax rate is 20%. In the function, define and use the tax variable with a value of 0.2.

Stuck? Here's a hint!

Use the following function:

round(number numeric, round_to_n_places integer)