PL/pgSQL
Overview & Programming Structures
12
Copyright
© Postgres Professional, 2017–2021
Authors: Egor Rogov, Pavel Luzanov
Translated by Liudmila Mantrova
Usage of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed on an unrestricted basis. Commercial use is only possible with prior
written permission of Postgres Professional company. Modification of course
materials is forbidden.
Contact Us
Please send your feedback to: edu@postgrespro.ru
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
Agenda
PL/pgSQL history
Block structure and declaration of variables
Anonymous blocks
Functions in PL/pgSQL
Conditional operators and loops
Computing expressions
3
The History of PL/pgSQL
First appeared in version 6.4 in 1998
is installed by default since version 9.0
Objectives
create a simple language for user-defined functions and triggers
add control structures to the SQL language
keep the opportunity to use any user-defined types, functions, and operators
Ancestors: Oracle PL/SQL, Ada
PL/pgSQL is one of the first procedural languages for PostgreSQL.
It appeared in 1998 in version 6.4, and starting from version 9.0 it is
installed by default when a database is created.
PL/pgSQL extends the SQL functionality, providing such capabilities of
procedural languages as using variables and cursors, conditional operators,
loops, error handling, etc.
PL/pgSQL is based on the Oracle PL/SQL language, which, in its turn, is
derived from a subset of the Ada language. This branch stems from such
languages as 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 unusual and excessively verbose (its distinctive
feature is using BEGIN and END keywords instead of curly brackets). Yet its
syntax goes well with SQL.
4
Block Structure
A 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, references to object types (%TYPE) are allowed
Operators
Control structures
SQL operators, except for the service ones
Handling exceptions
PL/pgSQL operators are organized into blocks. We can single out the
following components in the block structure:
- An optional label that can be used to eliminate naming ambiguities.
- An optional section for declaration of local variables and cursors. You can
use any types defined in SQL. It is also possible to refer to the type of a
table column using the %TYPE construct.
- 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. It is
forbidden to use SQL service commands, such as VACUUM. As for
transaction control commands (such as COMMIT and ROLLBACK), they are
allowed only in procedures.
A nested PL/pgSQL block can also be used as an operator.
5
Anonymous Blocks
One-time execution of procedures
without creating a stored routine
with no parameters
with no return values
DO operator of the SQL language
It is possible to use PL/pgSQL without creating routines. The PL/pgSQL
code can be written as an anonymous block and executed using the DO
command of the SQL language.
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 allow passing parameters or returning values. But there are
indirect ways to achieve the same outcome, e.g., using tables.
7
PL/pgSQL Routines
A routine header does not depend on the language
name, input and output parameters
for functions: return value and volatility category
Specifying LANGUAGE plpgsql
Returning values
the RETURN operator
assigning values to the output parameters (INOUT, OUT)
We have already learned about stored functions and procedures using the
SQL language as an example. Most of the covered information related to
routines’ creation and management applies to PL/pgSQL routines as well,
such as:
- creating, modifying, and deleting routines
- system catalog location (pg_proc)
- parameters
- return value and volatility categories (for functions)
- overloading and polymorphism
While SQL routines return a value produced by the last SQL operator,
PL/pgSQL routines either have to assign return values to formal INOUT or
OUT parameters, or use a special RETURN operator (which is available for
functions).
9
Conditional Operators
IF
a regular conditional operator
CASE
is similar to CASE in the SQL language, but does not return a value
Attention: three-valued logic
a condition must be true; false and NULL are ignored
PL/pgSQL provides two conditional operators: IF and CASE.
The first one is an absolutely standard operator, which is available in all
languages.
The second one works similar to CASE in SQL, but it’s a proper operator
that does not return a value. It is somewhat analogous to the switch
operator in C or Java.
It’s important to always remember that boolean expressions in SQL (and,
consequently, in PL/pgSQL) can take three values: true, false, and NULL.
A condition is triggered only if it is true; it won’t be triggered if it is false or
undefined. It is equally applicable to both WHERE conditions in SQL and
conditional operators in PL/pgSQL.
11
Loops
A FOR loop over a range of numbers
A WHILE loop with a precondition
An infinite loop
A loop can have its own label, just like any block
Control
exiting a loop (EXIT)
initiating 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 numbers
- a WHILE loop with a precondition
- an infinite loop
A loop is a special kind of a block; it can have its own label.
You can additionally control loop execution by initiating a new iteration or
terminating the loop.
In addition to working with ranges of numbers, FOR loops can iterate
through query results and arrays. These flavors of the FOR loop will be
discussed in the next lectures.
13
Computing Expressions
Any expression is computed in the context of SQL
an expression is automatically converted into a query
a query is getting prepared
PL/pgSQL variables become implicit parameters
Distinctive features
you can use all SQL capabilities, including subqueries
the execution speed is quite low,
although the parsed query (and sometimes the query plan) is cached
naming ambiguities have to be taken care of
All expressions that occur in the PL/pgSQL code are computed as SQL
queries to the database. The interpreter builds the required query by
substituting PL/pgSQL variables with parameters, prepares the operator
(while the parsed query is being caсhed, as it is usually done for prepared
operators), and executes it.
Although it’s not good for PL/pgSQL performance, it ensures very close
integration with SQL. In fact, expressions can use any SQL functionality
without limitations, including calling built-in and user-defined functions,
running subqueries, etc.
15
Summary
PL/pgSQL is installed by default and is integrated with SQL;
it is a convenient and easy-to-use language
The process of managing routines in PL/pgSQL is the same as
in 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 shortened by a full word.
Check your implementation.
Task 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:
- This function can be used somewhere else.
- Each function will perform exactly one task.
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 well 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. A shell game problem.
One of the three shells contains a pea.
A player selects one of the shells. The operator removes one of
the two remaining shells (which must be empty) and gives the
player an opportunity to change the choice, i.e., select the other
shell from the remaining two.
Does it make sense to change the choice, or is it better to keep
the initial one?
Assignment: using PL/pgSQL, estimate the probability of the
win for both the first and second choices.
You can first create the rnd_integer function that returns a random
integer within the specified range. This function will be useful for solving
both problems.
For example: rnd_integer(30, 1000) → 616
Task 1. Apart from the string length, you can also provide the list of allowed
characters as an input parameter. By default, it can be all alphabetic
characters, digits, and some other special characters. To select random
characters from the list, you can use the rnd_integer function. A function
declaration can look as follows:
CREATE FUNCTION rnd_text(
len int,
list_of_chars text DEFAULT
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_0123456789'
) RETURNS text AS ...
An example of the function call: rnd_text(10) → 'LjdabF_OОJ'
Task 2. You can use an anonymous block in your solution.
First, you have to develop an individual game run and check which choice
has won: the initial or the modified one. For setting and guessing the
winning shell you can use rnd_integer(1,3).
Then place the game into a loop and iterate through it, e.g., 1000 times,
counting wins for each choice. Finally, use RAISE NOTICE to display the
counter values and determine the winner (or lack thereof).
