PL/pgSQL
Executing Queries
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
Using SQL commands in PL/pgSQL code
Eliminating naming ambiguities
Checking command status
Table functions
3
Returning no Rows
SQL commands are embedded into PL/pgSQL code
as in expressions:
the query is prepared, PL/pgSQL variables become parameters
SELECT → PERFORM
it is 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 control: 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. It allows caching the
parsed (or planned) query to avoid repeating this work. Commands can also
use PL/pgSQL variables, which will be implicitly converted to parameters.
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).
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
getting the first returned row
one variable of a composite type
or an appropriate number of scalar variables
INSERT, UPDATE, DELETE RETURNING … INTO
getting 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 control the output order using 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
command 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 should be considered 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 other ones).
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, and 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
do 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 FOUND boolean variable, which shows
whether the command has processed any data at all.
You can 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
Distinctive 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 set-returning function in PL/pgSQL, you have to declare it with
RETURNS SETOF or RETURNS TABLE clauses (just like in SQL).
To return a set of rows, a function must use a special construct RETURNS
QUERY query. It will return almost the same result as an SQL function
containing the same query, but it does not support inlining (while the SQL
function query has all the chances to be inserted into the main query).
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. The RETURN NEXT command (and RETURN QUERY as well) can be
called several times.
The final result will be returned only after the function execution is fully
complete (you can use a regular RETURN command for this purpose).
In other words, RETURN NEXT is different from yield in generator
functions provided by modern languages.
11
Summary
PL/pgSQL is closely integrated with SQL
procedural code can contain queries
(provided as expressions or as separate commands)
queries can use variables
you can get query results and check query status
You have to take care of resolving naming ambiguities
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 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.
Task 1.
FUNCTION add_author(last_name text, first_name text, surname text)
RETURNS integer
Task 2.
FUNCTION buy_book(book_id integer)
RETURNS void
You can notice that the application allows selling more books than actually
available. 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” lecture.
13
Practice
Create a game in which the server tries to guess the animal chosen by
user by repeatedly asking clarifying yes-no questions.
If the server suggests a wrong answer, it requests the user to provide
the animal name and then asks a different question. 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 required functions.
3. Check your implementation.
A possible dialog (between people):
— Is it a mammal? — Yes.
— Is it an elephant? — No.
— I give up. Who is it? — It’s a whale.
— How can we tell a whale from an elephant? — It lives in the ocean.
Task 1. It is convenient to present this information as a binary tree. Inner
nodes store questions, while leaf nodes store animal names. One of the
child nodes corresponds to “yes,” the other one corresponds to “no.”
Task 2. Between the function calls, it is required to pass the information
about the last node of the tree we have got to (the context of the dialog). For
example, we 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, issue 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
