Postgres PL/pgSQL Structure
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
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.
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:
[ <<block_label>> ]
END [ block_label ];
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
EXITstatement or to qualify
variablesthat 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
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.
In this section, we’ll look at an example that shows how to create a simple block:
first_name := 'Rommel';
last_name := 'Galisanao';
full_name := first_name || last_name;
RAISE NOTICE 'Hi %', full_name;
END first_block $$;
Let’s discuss the syntax shown above:
- First, we declare our variables:
full_name. These variables are assigned a
varchartype within the DECLARE section which is located right below the phrase
- 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 NOTICEcommand followed by the expression that we wish to display.
- Finally, we end the block using
ENDfollowed by the double dollar sign.
The output of this block should look like the following:
NOTICE: Hi RommelGalisanao
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.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started