PL/pgSQL
Overview and Programming Structures
16
Copyright
© Postgres Professional, 2017–2025
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova
Photo by: Oleg Bartunov (Phu monastery, Bhrikuti summit, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and losses,
including loss of income, caused by direct or indirect, intentional or accidental
use of course materials. Postgres Professional company specifically disclaims
any warranties on course materials. Course materials are provided “as is,” and
Postgres Professional company has no obligations to provide maintenance,
support, updates, enhancements, or modifications.
2
Topics
PL/pgSQL History
Block Structure and Declaration of Variables
Anonymous Blocks
Routines in PL/pgSQL
Conditional Statements and Loops
Expression Computing
3
PL/pgSQL History
Introduced in PostgreSQL 6.4 in 1998
comes out of the box since PostgreSQL 9.0
Objectives
create a simple language for custom functions and triggers
add control structures to the SQL language
keep the ability to use any custom types, functions, and statements
Inspired by: Oracle PL/SQL, Ada
PL/pgSQL is one of the first procedural languages for PostgreSQL. It first
appeared in 1998 in PostgreSQL 6.4, and since 9.0, it has been installed by
default when a database is created.
PL/pgSQL extends the SQL functionality, providing variables and cursors,
conditional statements, loops, error handling, and other features commonly
seen in procedural languages.
PL/pgSQL is based on the Oracle PL/SQL language, which, in turn, is
derived from a subset of the Ada language, with its roots going back to Algol
and Pascal. Most of the modern programming languages belong to another
branch of the C-like languages, that’s why PL/pgSQL can at first seem
unconventional and excessively verbose (its distinctive feature is using
BEGIN and END keywords instead of curly brackets). However, this syntax
goes perfectly with the SQL syntax.
4
Block Structure
Block label
Variable declarations
variable scope is a block
the name can be overridden by a nested block,
but a variable can still be qualified by a block label
any SQL types and references to object types (%TYPE) are allowed
Statements
control structures
SQL statements, except for the service ones
nested blocks
Exception handling
PL/pgSQL statements are organized into blocks. A block structure
comprises several components:
●
An optional label that can be used to eliminate naming ambiguities.
●
An optional section for declaration of local variables and cursors. Any
types defined in SQL are allowed. You can also use the %TYPE
construct to refer to the type of a table column or other object.
●
The main execution section that contains statements.
●
An optional section for handling exceptions.
You can use both PL/pgSQL commands and most of SQL commands as
statements, so the two languages are integrated almost seamlessly.
Exceptions are SQL service commands, such as VACUUM, which are not
allowed, and transaction control commands, such as COMMIT and
ROLLBACK, which are allowed only in procedures.
Another (nested) PL/pgSQL block can also be used as a statement.
5
Anonymous Blocks
Ad-hoc execution of procedures
without creating a stored routine
with no parameters
with no return values
The DO command in the SQL language
You can use PL/pgSQL without creating routines. The PL/pgSQL code can
be written as an anonymous block and executed using the SQL’s DO
command.
This command can be used with various server languages, but if you do not
specify the language explicitly, it will be assumed that PL/pgSQL is used.
The code of anonymous blocks is not saved on the server. Anonymous
blocks do not take arguments or return any values (but there are ways to
circumvent that: for example, by using tables).
7
PL/pgSQL Routines
A routine header is language-agnostic
name, input and output parameters
for functions: the return value and volatility category
LANGUAGE plpgsql clause
Returning values
RETURN statement
assigning values to output parameters (INOUT, OUT)
We have already learned about stored functions and procedures, using the
SQL language as an example. Most of the information related to creation
and management of routines applies to PL/pgSQL routines as well:
●
Creating, modifying, and deleting routines
●
Location in the system catalog (pg_proc)
●
Parameters
●
Return value and volatility categories (for functions)
●
Overloading and polymorphism
●
Etc.
While SQL routines return a value produced by the last SQL command,
PL/pgSQL routines either have to assign return values to INOUT or OUT
parameters, or use a special RETURN statement (which is available for
functions).
9
Conditional Statements
IF
a regular conditional statement
CASE
similar to CASE in the SQL language, but does not return a value
Note: three-valued logic!
the condition must be true; false and NULL are ignored
PL/pgSQL provides two conditional statements: IF and CASE.
The first one is the bread and butter statement available in all languages.
The second one is similar to the SQL CASE construct, but it is a statement
and so it does not return a value. It is unlike the switch statements in C or
Java.
Remember that boolean expressions in SQL (and, consequently, in
PL/pgSQL) can take three values: true, false, and NULL. A condition is
triggered only when it is true, and is not triggered when it is false or
undefined. This is equally applicable to both WHERE conditions in SQL and
conditional statements in PL/pgSQL.
11
Loops
FOR loop over a range of integers
WHILE loop with a precondition
Infinite loop
Loop can have its own label, just like any block
Loop controls
exit a loop (EXIT)
begin a new iteration (CONTINUE)
For repeated execution of a set of statements, PL/pgSQL offers several
types of loops:
●
A FOR loop over a range of integers
●
A WHILE loop with a precondition
●
An infinite loop
A loop is a type of a block; it can have its own label. You can additionally
control loop execution by initiating a new iteration or exiting the loop.
In addition to working with integer ranges, FOR loops can iterate through
query results and arrays. Such FOR loops will be discussed later on.
13
Expression Computing
Any expression is computed in the context of SQL
an expression is automatically converted into a query
the query is prepared
PL/pgSQL variables are substituted as parameters
Features
you can use all SQL capabilities, including subqueries
the execution speed is lower
although the parsed query (and sometimes the query plan) is cached
naming ambiguities are an issue
All expressions in PL/pgSQL code are computed as SQL database queries.
The interpreter builds the query by composing a SELECT <expr> statement
and putting parameters in place of PL/pgSQL variables. Next, the statement
is prepared (parsed query is cached as usually) and planned. When the
statement is executed, specific values are bound to the parameters, and
planning is redone (if PostgreSQL has the query plan cached as well, this
step may be skipped).
While executing SQL queries impacts PL/pgSQL performance, it ensures
close integration with SQL. In fact, expressions can leverage any SQL
functionality without limitations, including calling built-in or custom functions,
running subqueries, etc.
Starting with PostgreSQL 14, the execution of simple expressions (at least
those that do not access any tables) has been optimized: such expressions
are processed by the server's parser directly, without using the planner at
all.
15
Takeaways
PL/pgSQL is an easy-to-use language that comes with the
system by default, integrated with SQL
Managing routines in PL/pgSQL is similar to other languages
DO is an SQL command for executing anonymous blocks
PL/pgSQL variables can use any SQL types
PL/pgSQL supports regular control structures, such as
conditional statements and loops
16
Practice
1. Modify the book_name function, so that the length of the return
value does not exceed 47 characters.
If the book title gets truncated, it must be concluded with an
ellipsis.
Check your implementation in SQL and in the application. Add
more books with long titles if required.
2. Modify the book_name function again, so that an excessively
long title gets cut off at the end of a full word.
Check the implementation.
1. For example:
Travels into Several Remote Nations of the World. In Four Parts.
By Lemuel Gulliver, First a Surgeon, and then a Captain of Several
Ships →
→ Travels into Several Remote Nations of the W...
Here are some cases that are worth checking for:
●
The title length is less than 47 characters (should not change).
●
The title length is exactly 47 characters (should not change).
●
The title length is 48 characters (four characters have to be truncated
because three dots will be added).
It is recommended to implement and debug a separate function for
truncation, and then use it in book_name. It is useful for other reasons as
well:
●
It may come in handy somewhere else.
●
Each function will perform exactly one task.
2. For example:
Travels into Several Remote Nations of the World. In Four Parts.
By Lemuel Gulliver, First a Surgeon, and then a Captain of Several
Ships →
→ Travels into Several Remote Nations of the...
Will your implementation work properly if the title consists of a single long
word without spaces?
17
Practice+
1. Create a PL/pgSQL function that returns a string of random
characters of the specified length.
2. The Monty Hall problem.
There are three doors. Behind one of the doors is a prize.
The player selects one of the doors. The game host opens one
of the two remaining doors that does not contain the prize, and
gives the player an opportunity to change the choice — that is,
to select the other door from the remaining two.
Should the player switch the choice, or is it better to stick with
the initial one?
Task: using PL/pgSQL, calculate the probability of winning for
both situations: keeping the initial choice and switching doors.
You can first create the rnd_integer function that returns a random integer
within the specified range. You can use the function for both problems.
For example: rnd_integer(30, 1000) → 616
1. In addition to specifying the string length, you can also provide the list of
allowed characters as an argument. By default, the list can be all Latin
letters, digits, and common special characters. To select random characters
from the list, you can use the rnd_integer function. The function declaration
can look as follows:
CREATE FUNCTION rnd_text(
len int,
list_of_chars text DEFAULT
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_0123456789'
) RETURNS text AS ...
Function call example: rnd_text(10) → 'LjdabF_OОJ'
2. You can use an anonymous block for this one.
First, implement one round of the game and see which option won: the initial
one or the modified one. You can use rnd_integer(1,3) for setting and
guessing the winning door.
Then run the game in a loop for a thousand times or so, counting wins for
each strategy. Finally, use RAISE NOTICE to display the counter values and
determine the optimal choice (or lack thereof).
