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
4. Purpose of functions
Summary

Instruction

What are the benefits of using functions? Let's list the main advantages:

  • Functions extend the functionality of a database (we are allowed to add custom functions).
  • A function allows you to encapsulate logic that can be invoked like a programming subroutine.
  • Code reusability. Imagine that we have to repeat some calculations in our SQL queries. It would be tedious to manually hard-code these calculations. Instead, we can simply create a generic function that takes some inputs and performs the calculations for us.

Warning (portability): each database engine (for instance Oracle, SQL Server, etc.) has slightly different syntax for creating functions, though the concepts are similar in all database engines.

Exercise

Select the id, radius, diameter, and computed circumference and area of each record from the circle table (using our newly-created get_pi() function).

Note:

  • The circumference of a circle equals pi * diameter.
  • The area of a circle equals pi * radius * radius.

Mind the order of multiplying – it matters when it comes to SQL and floating-point numbers.