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
5. Function syntax
Summary

Instruction

It's time to write your first function. Before you do so, let's look in more detail at the syntax of a function.

Every function has a name, body, some optional arguments, and a return type.

In PostgreSQL, we can use many languages to create functions (Perl, Python, etc.). However, PL/pgSQL is the most common language in PostgreSQL, and it's the one we'll be using in this course. The syntax of PL/pgSQL is modelled after Oracle's procedural language PL/SQL (hence the name PL/pgSQL):

CREATE FUNCTION function_name (arg1 type, ... , argN type)
RETURNS return_type AS $$
BEGIN
  -- function body
END;
$$ LANGUAGE PLpgSQL;

The syntax of creating functions in PostgreSQL is a bit verbose. First we use the CREATE FUNCTION keyword. Then we specify the name of the function and its parameters in parentheses. Next, after the RETURNS keyword, we specify the return type of the function.

The body function is surrounded with a bit of boilerplate code: AS $$ BEGIN before the function body and END; $$ after the function body. Finally, after the LANGUAGE keyword, we specify the programming language we use to create the function (PLpgSQL in our case).

Exercise

Create a function named get_circumference() that takes one argument named diameter of type decimal and returns a circumference value of type decimal. Use the get_pi() function we created in a previous exercise.

Stuck? Here's a hint!

Don't know how to do it? Here's the correct answer:

CREATE FUNCTION get_circumference(diameter decimal)
RETURNS decimal AS $$
BEGIN
  RETURN get_pi() * diameter;
END;
$$ LANGUAGE PLpgSQL;