PL/pgSQL
Query Execution
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
Using SQL Commands in PL/pgSQL Code
Eliminating Naming Ambiguities
Checking Command Status
Set Returning Functions
3
Returning Nothing
SQL commands are embedded into PL/pgSQL code
same as in expressions:
the statement is prepared, PL/pgSQL variables become parameters
SELECT → PERFORM
convenient for calling functions with side effects
WITH queries should be wrapped into a SELECT
INSERT, UPDATE, DELETE and other SQL commands
except for service commands
transaction management: only in procedures and anonymous blocks
As we have already seen, PL/pgSQL is very closely integrated with SQL.
In particular, all expressions are computed using prepared SQL statements.
Besides, expressions can use PL/pgSQL variables and routine parameters:
they will be implicitly converted to query parameters.
SQL statements can also be executed within PL/pgSQL code. To execute a
statement that returns no result (INSERT, UPDATE, DELETE, CREATE,
DROP, etc.), you just need to write an SQL command within the PL/pgSQL
code as a separate statement.
Commands are prepared exactly like expressions, with PL/pgSQL variables
used as parameters. It allows caching the parsed (or planned) query to
avoid repeating this work.
In a similar manner, you can also execute a regular SELECT statement if its
result is unimportant; simply replace the SELECT keyword with PERFORM.
It makes sense for cases like calling functions with side effects. WITH
queries have to be wrapped into a SELECT statement (so that it starts with
PERFORM when executed).
Service commands like VACUUM, REINDEX and others are not allowed in
routines.
COMMIT and ROLLBACK commands are allowed only in procedures and
anonymous blocks (executed by the SQL command DO).
5
Returning a Single Row
SELECT … INTO
get the first returned row
one variable of a composite type
or an appropriate number of scalar variables
INSERT, UPDATE, DELETE RETURNING … INTO
get the inserted (updated, deleted) row
one variable of a composite type
or an appropriate number of scalar variables
If the query result is important, you can use the INTO clause to save it into a
single variable of a composite type or into several scalar variables. If the
query returns several rows, only the first one makes it into the variable (you
can set the output order with the ORDER BY clause). If the query returns no
rows, the variables (or the attributes of the composite type variable) will be
set to NULL.
In a similar manner, you can also use INSERT, UPDATE, and DELETE
commands with the RETURNING clause. The difference is that these
commands must not return more than one row: it will lead to an error as
there is no way to specify which row is the first one.
If a query returns several rows, but only one of them is used, it’s highly likely
that this query is incorrect. PL/pgSQL can report such suspicious situations
(and several others).
More powerful debugging capabilities are provided by an external extension
plpgsql_check (developed by Pavel Stehule).
7
Checking Results
INTO STRICT
guarantees that exactly one row is returned, no more and no less
ROW_COUNT diagnostics
the number of rows returned (processed) by the last SQL command
FOUND variable
after an SQL command: true if the command has returned (processed) a row
after a loop: indicates that at least one iteration has been completed
By adding the STRICT keyword to the INTO clause, we can guarantee that
the command returns or processes exactly one row; otherwise, an error
occurs.
Besides, we can check the status of the SQL command that has just been
executed (unless it has completed with an error). There are two ways to go
about it.
The first option is to use the GET DIAGNOSTICS clause to get the number
of rows processed by the command.
The second option is to use the boolean variable FOUND, which shows
whether the command has processed any data at all.
You can also use FOUND as an indicator that the loop body has been
executed at least once.
9
Set Returning Functions
Query rows
RETURN QUERY query;
One row
RETURN NEXT expression; if there are no output parameters
RETURN NEXT; if there are output parameters
Features
rows are added to the result, but function execution is not terminated
commands can be executed several times
the result is not returned until the function completes
To create a table function in PL/pgSQL, you have to declare it with a clause
RETURNS SETOF or RETURNS TABLE (just like in SQL).
Commands RETURN QUERY and RETURN NEXT assemble individual
elements of the returning value in a special buffer. These commands do not
interrupt execution. They can be called multiple times within a function body,
each call adding elements to the final output. When a function finishes (by a
RETURN call with no parameters or simply by reaching the end of the
function body), the output that has been assembled in the buffer is returned.
If a function finishes after executing the RETURN QUERY query command
once, the output will be identical to that of an SQL function running the
query as its last. However, the query from an SQL function could be
substituted into the main query, while with PL/pgSQL this is not the case.
The result can also be returned row by row using the RETURN NEXT
construct. It is similar to an ordinary RETURN, but instead of stopping the
function execution, it adds the return value as another row of the future
result.
Thus, the RETURN NEXT and RETURN QUERY commands do not work as
yield in the generator functions of modern languages.
11
Takeaways
PL/pgSQL is closely integrated with SQL
procedural code can contain queries
(provided as expressions or as separate commands)
queries can include variables
you can get query results and check query status
Naming ambiguities are an issue
12
Practice
1. Create the add_author function for adding new authors. The
function must take three parameters (last name, first name,
middle name) and return the ID of the added author.
Check that the application allows adding new authors.
2. Create the buy_book function for buying books. The function
takes the book ID as a parameter and reduces the number of such
books by one. There is no return value.
Check that the Store tab now allows buying books.
1.
FUNCTION add_author(last_name text, first_name text, middle_name
text)
RETURNS integer
3.
FUNCTION buy_book(book_id integer)
RETURNS void
You can notice that the application allows selling more books than there are
in store. If the total number of books were stored in a table column, adding a
CHECK constraint would be a good and simple solution. But our
implementation calculates the number of books, so we are not going to
address this until we get to the Triggers lesson of the course.
13
Practice+
Create a game in which the server tries to guess the animal chosen by
the user by asking a series of clarifying questions that can be
answered with a “yes” or a “no”.
If the server suggests a wrong answer, it requests the user to provide
the animal name, and then asks how is it different from its
suggestion. This new information is registered for use in the next
games.
1. Create a table for data representation.
2. Design an interface and implement all the necessary functions.
3. Check the implementation.
A possible dialog (between humans):
— Is it a mammal? — Yes.
— Is it an elephant? — No.
— I give up. What is it? — It’s a whale.
— How is it different from an elephant? — It lives in water.
1. The data falls nicely into a binary tree structure. Inner nodes store
questions, while leaf nodes store animal names. One of the child nodes
corresponds to the answer “yes”, the other to the answer “no”.
2. Between the function calls, you need to pass the information about the
last node of the tree the user gets to (the context of the dialog). For
example, you could have the following functions:
●
start the game (no input context):
FUNCTION start_game(OUT context integer, OUT question text)
●
continue the game (get the answer, ask the next question):
FUNCTION continue_game(
INOUT context integer, IN answer boolean,
OUT you_win boolean, OUT question text)
●
end the game (add information about another animal):
FUNCTION end_game(
IN context integer, IN name text, IN question text)
RETURNS void