SQL
Procedures
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
Procedures and their differences from functions
Input and output parameters
Overloading and polymorphism
3
Routines
Functions
are called in the context of an expression
cannot manage transactions
return a result
Procedures
are called using the CALL operator
can manage transactions
can return a result
Procedures were first introduced in PostgreSQL 11. The main reason for
their appearance is that functions cannot manage transactions. Functions
are called in the context of some expression that 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. So we won’t be able to see an example of a
procedure that manages transactions until we get to the “PL/pgSQL.
Executing Queries” lecture.
Sometimes you can hear that unlike functions, procedures do not return
a result. But it is not true: procedures can also return a result, if required.
A generic term for functions and procedures is routine.
5
Overloading
Several routines with the same name
routines differ in input parameter types;
types of the return value and output parameters are ignored
an appropriate routine is selected during execution based on the actual
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 signature consists of its name and
types of its input parameters.
When calling a routine, PostgreSQL finds its version that corresponds to the
passed arguments. There might be situations when an appropriate routine
cannot be determined unambiguously; in this case, a run-time error occurs.
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. Besides, when applied to functions, this command does not allow
changing types of the output parameters and the return value type.
So you should delete and recreate the routine if required, but it won’t be the
same routine anymore. When deleting an old function, you also have to
delete all views, triggers, and other objects that depend on it
(DROP FUNCTION ... CASCADE).
6
Polymorphism
A routine that takes parameters of various types
formal parameters use polymorphic pseudotypes
(such as anyelement)
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
parameters of any (or almost any) type.
For this purpose, a special polymorphic pseudotype is specified as the type
of the formal parameter. For now, we’ll only work with the anyelement
type, which corresponds to any base type; but later we’ll come across some
other pseudotypes.
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 has several polymorphic parameters, the types of the passed
arguments must be the same. In other words, in each routine call,
anyelement stands for some particular data 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.
8
Summary
You can create and use your own procedures
Unlike functions, procedures are called using the CALL operator
and can manage transactions
Both procedures and functions support overloading and
polymorphism
9
Practice
1. In the authors table, authors’ full 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.
Task 1. The feature for adding new authors won’t appear in the application
until we get to the “PL/pgSQL. Executing Queries” lecture. For now, you can
insert duplicates manually to check your solution.
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?
Check your response.
2. A table stores real numbers (for example, the results of some
measurements). 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 chosen normalization factor.
Task 2. As a normalization factor, use the maximum absolute value stored in
the table.
