Postgres PL/pgSQL Structure

Introduction

If you’re getting started with PL/pgSQL, the procedural language supported by PostgreSQL, it’s important to understand how to use the language’s control structures. Many key components fall into the category of control structures: commands that return data from a function, conditional operators such as IF and CASE, simple loop constructs and much more. These structures allow you to manipulate data from your PostgreSQL database in powerful ways. In this article, we’ll provide an introduction to Postgres PL/pgSQL control structures and review some code examples to understand how the structures work.

Prerequisite

Before we dive into our examples of Postgres PL/pgSQL control structures, let’s take a moment to go over the prerequisites that are required for this task:

• You’ll need to ensure that PostgreSQL is installed and configured on your system.
• You’ll need some basic knowledge of PostgreSQL to get the most out of this tutorial.

What is Postgres PL/pgSQL?

PL/pgSQL, also known as Procedural Language/PostgreSQL, is a Postgres-supported procedural language. The goal of Postgres PL/PgSQL was to create a procedural language with the following features:

• allows the inheritance of all operators, functions and user-defined types
• can be trusted by the server
• can handle complex calculations
• allows users to create functions and procedures
• simple to use

One of the obvious features of PL/PgSQL is that it’s “block-structured”. Shown below is the basic form of a PL/pgSQL block:

Let’s take a closer look at this block format:

• Notice that a block label is placed at the beginning and at the end of the block. The label is optional, but it can be helpful in indicating the EXIT statement or to qualify variables that are defined within the block.

• Blocks are composed of two sections: the ‘declaration’ and the ‘body’. The ‘body’ is required, but the ‘declaration’ is optional. We end a block with a semicolon right after the END keyword.

• All required variables are included in the declaration section. We use a semicolon to end the declaration section.

• The body section holds all the statements and is ended with a semicolon.

PL/pgSQL example

In this section, we’ll look at an example that shows how to create a simple block:

Let’s discuss the syntax shown above:

• First, we declare our variables: first_name , last_name and full_name. These variables are assigned a varchar type within the DECLARE section which is located right below the phrase DIRECTION.
• In the body section found below the phrase BEGIN, we assigned values to the variables that correspond to their declared type. We then perform a simple concatenation operation using the || double bar operator.
• Next, we return the output using the RAISE NOTICE command followed by the expression that we wish to display.
• Finally, we end the block using END followed by the double dollar sign.

The output of this block should look like the following:

Notice that we used a double dollar sign to enclose the block– this symbol is used to bypass the escaping of single quotes that typically occurs in a PL/pgSQL block, a stored procedure or a function.

Conclusion

If you’re planning to use the PL/pgSQL procedural language to manipulate your PostgreSQL data, you’ll need to have a solid understanding of the language’s control structures. In this article, we looked at the format of a typical PL/pgSQL block in order to see how Postgres PL/pgSQL control structures are used. With the instructions and examples provided in this article, you’ll be ready to use PL/pgSQL with your own PostgreSQL database.