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

In some cases, you may want to declare a variable with a read-only value assigned so it cannot be modified after it's been initialized.

That's where the CONSTANT keyword comes into play. The syntax for a constant is very similar to that of a variable; all we need to add is the CONSTANT keyword after the variable name. See the example below, where we declared and set the constants named tax and months:

CREATE FUNCTION get_monthly_tax(salary decimal)
RETURNS decimal AS $$
DECLARE
  tax CONSTANT decimal := 0.2;
  months CONSTANT integer := 12;
  monthly_salary decimal;
BEGIN
  monthly_salary := salary / months;
  RETURN monthly_salary * tax;
END;
$$ LANGUAGE plpgsql;

SELECT
  get_monthly_tax(12000) AS expect_200,
  get_monthly_tax(0) AS expect_0;

Later on, if we were to try to change the value of the tax variable (or the months variable), we would get the following error:

ERROR:  "tax" is declared CONSTANT

Exercise

Create a new function named get_net_salary(emp_id integer) that calculates the net salary for the given employee.

Introduce a new constant named vat_value with a value of 0.1 (the tax should be set to 10% and shouldn't change throughout the function). Use the vat_value constant to calculate the net salary of the employee with the given ID. Round the result to 2 decimal places.