PL/pgSQL
Overview and Programming Structures
17
Copyright
© Postgres Professional, 2017–2025
Authors Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by Liudmila Mantrova and Alexander Meleshko
Photo by Oleg Bartunov (Tukuche peak, 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.com
Disclaimer
In no event shall Postgres Professional company be liable for any damages
or loss, including loss of profits, that arise from direct or indirect, special or
incidental 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 operators 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 operators
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 operators, 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.
https://postgrespro.com/docs/postgresql/17/plpgsql-overview
4
Block structure
Block label
Declaration of variables
the lifetime of a variable is limited to a block
the visibility scope can be overridden by a nested block, but a variable can
still be referenced by a block label
any SQL types and references to object types (%TYPE) are allowed
Operators
control structures
SQL operators, except for the service ones
nested blocks
Exception handling
PL/pgSQL operators 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 operators.
An optional section for handling exceptions.
You can use both PL/pgSQL commands and most of SQL commands as
operators, 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 an operator.
https://postgrespro.com/docs/postgresql/17/plpgsql-structure
https://postgrespro.com/docs/postgresql/17/plpgsql-declarations#PLPGSQL
-DECLARATION-TYPE
5
Anonymous blocks
Ad-hoc execution of procedures
without creating a stored routine
with no parameters
with no return values
The DO operator 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).
https://postgrespro.com/docs/postgresql/16/sql-do
7
PL/pgSQL routines
A routine header is language-agnostic
name, input and output parameters
for functions: the return value and volatility category
Definition: LANGUAGE plpgsql
Returning values
the RETURN operator
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 operator,
PL/pgSQL routines either have to assign return values to INOUT or OUT
parameters, or use a special RETURN operator (which is available for
functions).
9
Conditional operators
IF
a regular conditional operator
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 operators: IF and CASE.
The first one is the bread and butter operator available in all languages.
The CASE operator is similar to the SQL one, but does not return a value.
It is not unlike the switch operator 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 operators in PL/pgSQL.
https://postgrespro.com/docs/postgresql/17/plpgsql-control-structures#PLP
GSQL-CONDITIONALS
11
Loops
A FOR loop over a range of integers
A WHILE loop with a precondition
An infinite loop
A loop can have its own label, just like any block
Loop controls
exit a loop (EXIT)
initiate a new iteration (CONTINUE)
For repeated execution of a set of operators, 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.
https://postgrespro.com/docs/postgresql/17/plpgsql-control-structures#PLP
GSQL-CONTROL-STRUCTURES-LOOPS
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 preparing a SELECT <expr> statement
and puts parameters in place of PL/pgSQL variables. A prepared statement
is parsed once, and its parse tree is cached. When the statement is
executed, specific values are bound to the parameters, and planning is
redone (if PostgreSQL has the query plan cached, 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 query any tables) has been optimized: such expressions
are processed by the server's parser directly, without using the planner at
all.
https://postgrespro.com/docs/postgresql/17/plpgsql-expressions
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 operators 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?