PL/pgSQL
Query Execution
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
Using SQL commands in PL/pgSQL code
Eliminating naming ambiguities
Checking command status
Table functions
3
Returning nothing
SQL commands are embedded into PL/pgSQL code
same as in expressions:
the query 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 operators.
Besides, expressions can use PL/pgSQL variables and routine parameters:
they will be implicitly converted to query parameters.
SQL queries can also be executed within PL/pgSQL code. To execute a
query 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 operator.
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).
https://postgrespro.com/docs/postgresql/17/plpgsql-statements#PLPGSQLS
TATEMENTS-GENERAL-SQL
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 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”.
https://postgrespro.com/docs/postgresql/17/plpgsql-statements#PLPGSQL-
STATEMENTS-SQL-ONEROW
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).
https://postgrespro.com/docs/postgresql/17/plpgsql-development-tips#PLPG
SQL-EXTRA-CHECKS
More powerful debugging capabilities are provided by an external extension
plpgsql_check (developed by Pavel Stehule).
https://github.com/okbob/plpgsql_check
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 add the GET DIAGNOSTICS clause that returns the
number of rows processed by this 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.
https://postgrespro.com/docs/postgresql/17/plpgsql-statements#PLPGSQL-
STATEMENTS-DIAGNOSTICS
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.
https://postgrespro.com/docs/postgresql/17/plpgsql-control-structures#PLP
GSQL-STATEMENTS-RETURNING
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” section of the course.