SQL
Procedures
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
Procedures and their Differences from Functions
Input and Output Parameters
Overloading and Polymorphism
3
Routines
Functions
called in the context of an expression
cannot manage transactions
return a result
Procedures
called using a CALL statement
can manage transactions
can return a result
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 a special CALL statement. If this statement
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 with unquoted body 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.
5
Overloading
Several routines with the same name
routine is uniquely identified by a signature:
its name 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 combination of input parameter types,
creates a new overloaded routine
for existing combination 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).
6
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 consider just two of them — anyelement
and anycompatible — with more to follow in later sections.
A routine defined with polymorphic pseudotypes 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
routines with unquoted body, there is no way to use polymorphic data types
for arguments.
8
Takeaways
You can create and use custom procedures
Unlike functions, procedures are called using the CALL
statement and can manage transactions
Both procedures and functions support overloading and
polymorphism
9
Practice
1. In the authors table, authors’ first, last and middle names must be
unique, but this condition is not checked. Create a procedure that
deletes possible duplicates.
2. To eliminate the need for such a procedure, create a constraint
that will not allow entering duplicates in the future.
1. The feature for adding new authors won’t appear in the application until
we get to the PL/pgSQL. Query execution section. For now, you can insert
duplicates manually to check if your solution works.
10
Practice+
1. Is it possible to create the following objects with the same name
that belong to the same schema:
1) a procedure with one input parameter?
2) a function with one input parameter of the same type that
returns some value?
3) a procedure with one input and one output parameter?
Try and see.
2. A table stores real numbers (for example, measurements of some
sort). Create a procedure that performs data normalization by
multiplying all numbers by a certain factor, so that all values fit
the interval between −1 and 1.
The procedure must return the selected normalization factor.
2. Take the maximum absolute value from the table as the normalization
factor.
