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:
1 2 3 4 5 6 7 | [ <<block_label>> ] [ DECLARE declarations_goes_here ] BEGIN statements_goes_here; ... 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
EXIT
statement or to qualifyvariables
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:
1 2 3 4 5 6 7 8 9 10 11 12 | DO $$ <<first_block>> DECLARE first_name varchar(10); last_name varchar(10); full_name varchar(20); BEGIN 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:
first_name
,last_name
andfull_name
. These variables are assigned avarchar
type within the DECLARE section which is located right below the phraseDIRECTION
. - 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:
1 | 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.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started