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
26. Summary and Exercise 1

Instruction

Good job! Now, it's time to review what we've learned in this part:

  • We covered and practiced the syntax of CREATE FUNCTION. We focused on scalar functions (the most simple kind of user-defined functions that return a single value).
  • Basic PL/pgSQL syntax consists of:
    • Declaring variables (DECLARE keyword).
    • Declaring constants (CONSTANT keyword).
    • Assignments (we can assign a default value to a declared variable).
    • Conditional instructions (if-then, if-then-else, if-then-elsif).
    • Loops (WHILE, FOR).
    • Raising exceptions using the RAISE EXCEPTION or RAISE NOTICE keywords.
    • Handling exceptions to recover from exceptions by using a BEGIN block with the EXCEPTION clause.
  • We also explained and practiced function parameters: IN, OUT, INOUT, VARIADIC, and DEFAULT.
  • Postgres allows us to define more than one function with the same name, so long as the arguments are not the same. We say those functions are overloaded.

Let's go through the final recap exercises.

Exercise

Create a function with a signature of get_count_of_emp_since(month integer, year integer) that returns the number of employees who joined the company after a given month.

The function will return a number (integer type) of employees who joined during or after a given month and year.

Stuck? Here's a hint!

  1. Create a variable that will store the date created from the month and year arguments. To do so, use a regular string concatenation:

    d := year || '-' || month || '-01';

  2. Use the date_trunc('time_part', timestamp) function to get the month in which a given employee started the job, like so:

    date_trunc('month', start_date)

  3. Compare those two values. Remember: the start_date has to be greater than or equal to d.