SQL
Functions
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
Functions and their Specifics in Databases
Parameters and Return Values
Passing Arguments in a Function Call
Volatility Categories and Query Planning
3
Functions in Databases
The main goal is simplifying development tasks
interface (parameters) and implementation (function body)
abstracting from the main task when implementing a particular function
Traditional languages PostgreSQL
side global variables whole database
effects (volatility categories)
modules own interface namespaces,
and implementation client and server
challenges overhead hiding the query
related to calls from the planner
(inlining) (inlining, subqueries, views)
The main goal of introducing functions in programming is simplifying
development tasks by decomposing them into smaller subtasks. Such
simplification is possible because you can abstract from the big picture
when thinking of a function. For this purpose, the function provides a precise
interface to the outside world (parameters and the return value).
Its implementation (the function body) can change; the caller does not see
these changes and does not depend on them. This ideal situation can be
messed up by the global state (global variables), and you have to keep in
mind that in the DB context the whole database constitutes such a state.
In traditional programming languages, functions are often grouped into
modules (packages, classes for OOP, etc.), which have their own interface
and implementation. This separation into modules can be more or less
arbitrary. In PostgreSQL, there is a fixed boundary between the client and
the server: the server code deals with the database, while the client code
manages transactions. There are no modules (or packages), only
namespaces.
The only disadvantage of extensive use of functions in traditional languages
is function call overhead. It is sometimes overcome by inlining function code
into the calling program. In databases, the consequences can be more
serious: if some part of the query is moved into a function, the planner stops
seeing the big picture and cannot build a good query plan. In some cases,
PostgreSQL can also perform inlining; alternatively, subqueries or views can
be used.
4
Overview
A database object
function declaration is stored in the system catalog
The structure of function declaration
name
parameters
return data type
body
Can be written in various languages, including SQL
the code is stored as a string literal
a function is interpreted when it is called
Is called in the context of an expression
Functions are regular database objects, just like tables or indexes. Function
declarations are stored in the system catalog; that’s why database functions
are called stored functions.
PostgreSQL provides a lot of standard functions. Some of them are listed in
the Basic Data Types and Functions handout.
You can also write your own functions in various programming languages.
The information provided in this lecture applies to functions in any
programming language, but we will use SQL in all examples.
Predictably, a function declaration consists of a name, optional parameters,
a return data type, and a body. What may seem unexpected is that the body
is written as a string literal, which contains the code written in the
programming language of your choice. It makes function declarations look
the same regardless of the used programming language. The body string is
stored in the system catalog and is interpreted each time the function is
called. Since PostgreSQL 14, SQL code can be pre-parsed. In this case the
parse result is stored in the system catalog instead of the code itself.
Another way to avoid interpretation is to write a function in the C language,
but we are not going to discuss this approach here.
A function is always called within the context of an expression: in the list of
expressions of the SELECT statement, in the WHERE clause, in CHECK
constraints, etc.
6
Input and Output
Input values
defined by parameters with IN or INOUT modes
Output value
defined either by the RETURNS clause
or by parameters with OUT or INOUT modes
if both forms are specified, they must correspond to each other
Formal parameters that have IN or INOUT modes are input parameters.
Their actual values must be specified in the function call (or the default
values must be defined).
There are two ways to define the return value:
●
use the RETURNS clause to specify the return data type,
●
define output parameters using INOUT or OUT modes.
These two approaches are equivalent. For example, a function with the
RETURNS integer clause and a function with the OUT integer parameter
both return an integer number.
You can combine these two approaches. In this case, the function will also
return one integer number. But note that the types of the output parameters
and the RETURNS clause must not contradict each other.
Thus, unlike in many traditional programming languages, you cannot write a
function that returns one value while passing another value into the OUT
parameter.
8
Volatility Categories
Volatile
may return different values for the same input arguments
is used by default
Stable
the return value cannot change within a single SQL operator
the function cannot change the database state
Immutable
the return value cannot change, the function is deterministic
the function cannot change the database state
Each function is mapped to a particular volatility category, which defines the
properties of the return value for the same input arguments.
The volatile category means that the return value can change randomly.
Such functions will be executed each time they are called. If the function is
declared without a category specification, it is assumed to be volatile.
The stable category is used for functions that always return the same value
within a single SQL operator. In particular, such functions cannot change the
state of the database. PostgreSQL could execute such a function only once
during the query and then use the computed value.
The immutable category is even more strict: the return value always
remains the same. Such a function could be executed at the planning stage,
before the query is actually executed.
It does not mean that it happens so all the time, but the planner has the right
to perform such optimizations. In some (simple) cases, the planner makes
its own assumptions about function volatility, regardless of the explicitly
provided category.
10
Takeaways
You can create your own functions
Functions can be written in various languages, including SQL
Volatility categories affect optimization opportunities
An SQL function can sometimes be inlined
11
Practice
1. Create a function author_name to construct author names. The
function takes three parameters (last_name, first_name, and
middle_name) and returns the full name, with the middle name
abbreviated to its initial.
Use this function in the authors_v view.
2. Create a function book_name to construct book titles. The
function takes two parameters (book ID and title) and returns a
concatenation of the book title and the list of authors, ordered by
seq_num. The name of each author is produced by the
author_name function.
Use this function in the catalog_v view.
Check the changes in the application.
Reminder: all the required functions are listed in Basic Data Types and
Functions handout.
1. FUNCTION author_name(
last_name text, first_name text, middle_name text
)
RETURNS text
For example: author_name('Alexander','Sergeyevich', 'Pushkin')→
→ 'Alexander S. Pushkin'
3. FUNCTION book_name(book_id integer, title text)
RETURNS text
For example: book_name(3,'Good Omens') →
→ 'Good Omens. Terry Pratchett, Neil Gaiman'
Stored functions can be edited directly. For example, psql provides the \ef
command that opens the function source code in an editor and saves the
changes in the database.
You should avoid using this capability (or at least do not overuse it).
A properly set up development process requires that all the code is stored
in files under version control. If a function has to be changed, the file is
modified and executed (using psql or an IDE). Function modifications made
directly in the database can be easily lost. (In fact, setting up development
processes is much more complex, but we are not going to cover it in this
course.)
12
Practice+
1. Write a function that returns random timestamps,
equally distributed within the specified time range.
The lower bound of the range is set by a timestamptz value, while
the upper bound is either a timestamptz or an interval.
2. Sometimes, phonewords are used as a mnemonic equivalent of
telephone numbers, like 1-800-TAXICAB.
A phoneword is constructed by replacing some digits with the
corresponding letters, as seen on the keypad (even on modern
phones). Write a function that returns number representation.
3. Write a function that solves a quadratic equation.
In all tasks, make sure to pay attention to volatility categories of the
functions.
2. The correspondence between digits and letters is as follows:
2 – abc, 3 – def, 4 – ghi, 5 – jkl, 6 – mno, 7 – pqrs, 8 – tuv, 9 – wxyz
Note that a phoneword can be written in lowercase as well as in uppercase.
3. For the equation y = ax
2
+ bx + c the discriminant is D = b
2
– 4ac:
●
for D > 0, the two roots are x
1,2
= (–b ± √D) / 2a
●
for D = 0, the only root is x = –b / 2a (null can be returned as x
2
)
●
when D < 0 there are no roots (both roots are null)
