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

Perfect! Now we will talk about the basic syntax of the PL/pgSQL language.

The simplest instruction in any programming language is printing values. In PostgreSQL, you use the RAISE statement to report messages. The level typically used for displaying messages is NOTICE:

CREATE FUNCTION get_pi()
RETURNS decimal AS $$
BEGIN
  RAISE NOTICE 'The get_pi() function has been called';
  RETURN 3.14159265358979;
END;
$$ LANGUAGE plpgsql;

We'll talk about RAISE statement later in this section. For now, we'll use them to display messages.

Exercise

Create the hello_world() function, which will:

  1. Display the message 'Hello, World'.
  2. Return nothing (return type should be void).

Invoke the function and observe that it displays the notice, 'Hello, World'.

Note that if you SELECT a function that returns void, you'll see an empty column named as the function you called.