PL/pgSQL
Cursors
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
Motivation
Declaration and opening
Operations with cursors
Loops over cursors and query results
Passing a cursor to clients
3
Motivation
A cursor implies iterative processing
a full result set takes too much memory
only some part of the result set is needed, but its size is unknown in advance
it is required to allow the client to control the selection
row-by-row processing is really needed (such cases are quite rare)
We have already learned about the concept of cursors in “Architecture.
A General Overview of PostgreSQL.” In that lecture, cursors were
presented as a server feature, and we explained how to access them using
SQL. Now let’s talk about how to use cursors in the PL/pgSQL language.
Why do we need cursors at all? SQL is a declarative language; first and
foremost, it is designed to work with sets of rows, which is its strength and
advantage. Being a procedural language, PL/pgSQL has to work with data
row by row, using explicit loops. It can be achieved via cursors.
For example, a full SELECT result сan take too much memory, so it has to
be processed piece by piece. Or the size of the result set is unknown in
advance, i.e., it is required to stop the query at some point. Or control over
the query has to be delegated to the client.
(However, we would like to emphasize once again that although such row-
by-row processing may be really required from time to time, the same
outcome can often be achieved using pure SQL; as a result, the code will
become simpler and will work faster.)
4
Declaration and Opening
Unbound cursor variables
a variable of the refcursor type is declared
the actual query is specified when the cursor is being opened
Bound cursor variables
the query is specified at declaration time (parameters are also allowed)
the actual parameter values are passed when the cursor is being opened
Distinctive features
the value of the cursor variable is set to the cursor name
(can be specified explicitly or generated automatically)
PL/pgSQL variables become implicit parameters in the query (their values
are filled in when the cursor is being opened)
the query is prepared
Unsupported in SQL
As we have seen, SQL uses a single DECLARE command that both declares
and opens a cursor. In PL/pgSQL, these are two different steps. Besides,
there are so-called cursor variables that are used for cursor access. These
variables have the refcursor type and virtually contain the name of the
cursor (if you do not provide this name explicitly, PL/pgSQL ensures that it
is unique).
A cursor variable can be declared without being bound to a particular query.
Then you have to specify the query at the cursor’s opening time.
Alternatively, you can specify the query (including parameters) when
declaring a variable. Then you’ll only have to pass actual parameter values
when opening the cursor.
These methods are equivalent; which one to use is a matter of taste. In both
cases, a query can have implicit parameters, which are derived from
PL/pgSQL variables.
As noted above, a query opened with a cursor is prepared automatically.
6
Operations with Cursors
Fetching
row-by-row only
Accessing the current row of the cursor
is supported only for simple queries
(one table, no grouping or sorting)
Processing is usually performed in a loop
a FOR loop over a cursor
a FOR loop over a query without an explicitly declared cursor
Closing
explicitly or automatically at transaction end
In SQL,
DECLARE
WITH HOLD
In SQL,
selection size is
configurable
PL/pgSQL allows fetching data from a cursor row by row only. It is done via
the FETCH INTO command.
If the query is simple enough (it works with one table, without grouping or
sorting), it is possible to access the current row of the cursor in such
commands as UPDATE or DELETE.
Procedural processing implies looping through data. The rows returned by a
cursor can be iterated through and processed using control commands that
are already familiar to us. But since such loops are required quite often,
PL/pgSQL offers a special FOR command that implements them. We have
already seen an integer flavor of FOR in the “PL/pgSQL. Overview and
Programming Structures” lecture; this one works with cursors. Moreover,
there is one more flavor of the FOR loop that does not require cursor
declaration at all: the query is specified in the command itself.
A cursor can be explicitly closed by a CLOSE command, but it will be closed
anyway once the transaction is complete (in SQL, a cursor can remain open
even after the transaction has finished if you have specified WITH HOLD).
8
Passing a Cursor to a Client
backend
client
application
background process
PL/pgSQL function
refcursor
cursor (portal)
As we have already said, a PL/pgSQL cursor variable (of the refcursor
type) contains the name of an open SQL cursor. To denote the memory
allocated in the backend for keeping the cursor state, the portal term is
used.
Thus, a PL/pgSQL function can open a cursor and return its name to the
client. Then the client will be able to work with the cursor as if it has been
opened by this client, but will have access only to the provided data. It adds
one more way of setting up the interface between the database and the
application.
10
Summary
A cursor allows fetching and processing data row by row
A FOR loop can simplify cursor handling
Processing data in loops is common for procedural languages,
but should not be overused
11
Practice
1. Modify the book_name function: if the book has more than two
authors, the title should include only the first two, while the rest
are to be replaced with “et al.”
Check that the function works fine in SQL and in the application.
2. Try writing the book_name function in SQL.
Which implementation do you prefer: PL/pgSQL or SQL?
Task 1. For example:
101 Famous Poems. Alexander S. Pushkin, William Shakespeare, Ivan
A. Bunin →
→ 101 Famous Poems. Alexander S. Pushkin, William Shakespeare, et
al.
12
Practice
1. It is required to distribute energy expenses between different
departments in proportion to their headcount (the list of
departments is stored in a table).
Create a function that takes the total energy cost as an argument
and saves the distributed expenses in different table rows.
The values are rounded to cents; the sum of expenses of all
departments must exactly match the total cost.
2. Create a set-returning function that simulates merge sort.
The function should take two cursor variables; both cursors are
already open and return sorted integers in non-decreasing order.
It is required to return a single sorted sequence of integers from
both sources.
Task 1. We can use the following table:
CREATE TABLE depts(
id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
employees integer,
expenses numeric(10,2)
);
INSERT INTO depts(employees) VALUES (10),(10),(10);
A possible function implementation:
FUNCTION distribute_expenses(amount numeric) RETURNS void;
If 100.00 is taken as an argument, the expected result is:
expenses
----------
33.33
33.34
33.33
Task 2. A possible function implementation:
FUNCTION merge(c1 refcursor, c2 refcursor) RETURNS SETOF integer;
For example, if the first cursor returns the sequence 1, 3, 5, and the second
cursor returns the sequence 2, 3, 4, the expected result is as follows:
merge
-------
1
2
3
3
4
5
