SQL
Functions and Procedures
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
Functions and their specifics in databases
Parameters and return values
Passing arguments in a function call
Volatility categories and query planning
Procedures and their differences from functions
Overloading and polymorphism
3
Functions in databases
The main goal is simplifying development tasks
interface (parameters) and implementation (function body)
abstracting from other tasks 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
Functions 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.
https://postgrespro.com/docs/postgresql/17/sql-createfunction
https://postgrespro.com/docs/postgresql/17/sql-syntax-calling-funcs
6
Input and output
Input values
are defined by parameters with IN or INOUT modes
Output value
is defined either by the RETURNS clause
or by parameters with IN or INOUT modes
if both forms are specified, they must be logically equivalent
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.
https://postgrespro.com/docs/postgresql/17/xfunc-volatility
10
Procedures overview
The same structure of declaration
except for return data type
can return a result via OUT parameters
Is called using the CALL statement
Can manage transactions
except for SQL language
Procedures were first introduced in PostgreSQL 11. The main reason for
their introduction was that functions cannot manage transactions. Functions
are called in the context of some expression which is computed as part of
an already started operator (such as SELECT) in an already started
transaction. It is impossible to complete a transaction and then start a new
one while the operator is being executed.
Procedures are always called by the special CALL operator. If this operator
starts a new transaction (instead of being called from an already started
one), then it is possible to use transaction management commands in the
called procedure.
Unfortunately, procedures written in SQL cannot use COMMIT and
ROLLBACK commands (although those written in accordance with the new
SQL standard may be able to in the future). Therefore, we won’t see an
example of a procedure that manages transactions until we get to the
“PL/pgSQL. Query execution” section.
Some say that the difference between functions and procedures is that a
procedure does not return a result. But it is not true: procedures can also
return a result, if required.
An umbrella term for both functions and procedures is routines. They share
the common namespace.
https://postgrespro.com/docs/postgresql/17/sql-createprocedure
https://postgrespro.com/docs/postgresql/17/sql-call
12
Overloading
Several routines with the same name
routines differ in names and input parameter types
types of the return value and output parameters are ignored
an appropriate routine is selected during execution based on the argument
types
CREATE OR REPLACE command
for new combinations of input parameter types, creates a new overloaded
routine
for existing combinations of input parameter types, changes the
corresponding routine, but not the type of the return value
Overloading is the ability to use one and the same name for several routines
(functions or procedures), which differ in types of IN and INOUT
parameters.
In other words, a routine name and types of its input parameters form a
routine signature. When calling a routine, PostgreSQL finds its version that
corresponds to the passed arguments. If an appropriate routine cannot be
determined unambiguously, a runtime error occurs.
A signature, however, does not include:
routine type (procedure or function),
OUT parameter types,
returned value type.
You have to take overloading into account when executing CREATE OR
REPLACE (FUNCTION or PROCEDURE). If input parameter types differ
from those used by already existing routines, a new overloaded routine will
be created, otherwise a matching existing one will be replaced. Besides,
when an existing routine is replaced with the CREATE OR REPLACE
command, its type, OUT parameter types and return value type may not be
changed, but other properties such as the language can be. In some cases,
this means you must delete the routine and create it anew to replace it.
However, doing so requires you to first delete all dependent objects, such as
views, triggers, and other routines (DROP ROUTINE ... CASCADE).
https://postgrespro.com/docs/postgresql/17/xfunc-overload
13
Polymorphism
A routine that takes arguments of various types
formal parameters use polymorphic pseudotypes (such as anyelement or
anycompatible)
the actual data type is selected during execution based on the type of the
passed arguments
Instead of having several overloaded routines for different types, it is
sometimes more convenient to create a single routine that takes arguments
of any (or almost any) type.
For this purpose, a special polymorphic pseudotype is used as the formal
parameter type. For now, we will use just two of them anyelement and
anycompatible — with more to follow in later sections.
A routine defined with polymorphic pesudotypes as input parameters may
take any data type as input. The exact type to be used by the routine is
selected at run time based on the type of the passed argument.
If a routine is defined with multiple polymorphic parameters of the
anyelement type, all passed arguments will be implicitly converted to the
type of the first parameter. On the other hand, if a routine is defined with
multiple polymorphic parameters of the anycompatible type, all passed
arguments will be converted to some common type.
If a function is declared with a polymorphic return value, it must have at
least one polymorphic input parameter. The exact type of the return value is
also defined by the actual type of the passed input argument. For SQL
standard-style routines, there is no way to use polymorphic data types for
arguments.
https://postgrespro.com/docs/postgresql/17/extend-type-system#EXTEND-T
YPES-POLYMORPHIC
https://postgrespro.com/docs/postgresql/17/xfunc-sql#XFUNC-SQL-POLYM
ORPHIC-FUNCTIONS
15
Takeaways
You can create your own routines (functions and procedures)
Routines can be written in various languages, including SQL
Routines support overloading and polymorphism
Functions volatility categories affect optimization opportunities
An SQL function can sometimes be inlined
Unlike functions, procedures are called using the CALL
operator and can manage transactions
16
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 the 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 the “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 body 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.)