PL/pgSQL
Arrays
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
Arrays and their usage in PL/pgSQL
Loops over array elements
Functions with a variable number of arguments
and polymorphic functions
Array usage in tables
3
Array Type
Array
a set of numbered elements of the same type
one-dimensional, multidimensional
Initialization
usage without an explicit declaration (name-type[])
implicit declaration when creating a base type or a table (_name-type)
Usage
elements as scalar values
array slices
operations on arrays: comparison, inclusion, intersection, concatenation,
usage with ANY or ALL instead of a subquery, etc.
Just like a composite type (a record), an array is not a scalar; it consists of
several elements of another type. But unlike in records, a) all these
elements are of the same type, and b) they are accessed by an integer
index, not by name (here the term index is used in the mathematical sense
of the word, not in the sense of a database index).
An array type does not have to be explicitly declared; it is enough to append
square brackets to the name of the element type. (Besides, when any base
type or a table is created, a new array type is also declared; its name is
derived from the name of the element type by prepending an underscore to
it. But such naming is less intuitive.)
An array is a full-fledged SQL type: you can create table columns of this
type, pass arrays as function parameters, etc. Array elements can be
applied as regular scalar values. Array slices can also be used.
Arrays can be compared and checked for null; you can search arrays for
element inclusion and intersection with other arrays, perform concatenation,
etc. Arrays can also be applied in ANY/SOME and ALL constructs, similar to
subqueries.
You can find various array functions in course handouts.
5
Arrays and Loops
A regular loop over element indexes
array_lower
array_upper
A FOREACH loop over array elements
this approach is easier, but it does not provide access to indexes
To iterate through array elements, you can simply set up an integer FOR
loop using functions that return the minimum and the maximum index of the
array.
But there is also a specialized loop: FOREACH. In this case, a loop variable
iterates through the elements, not their indexes. That’s why the variable
must be of the same type as the array elements (as always, if the elements
are records, you can replace a single composite variable with several scalar
ones).
If a loop contains the SLICE clause, it will iterate through array slices.
For example, the rows of a two-dimensional array will be treated as its one-
dimensional slices.
7
Arrays and Routines
Routines with a variable number of arguments
all optional parameters must be of the same type
optional parameters are passed to the routine as an array
the last parameter must be of an array type and declared VARIADIC
Polymorphic routines
support values of various types;
the actual type is defined at run time
use additional polymorphic pseudotypes anyarray and anynonarray
can have a variable number of parameters
Using arrays, you can create routines (functions and procedures) with a
variable number of parameters.
While parameters with the default values have to be explicitly specified in
routine declaration, optional parameters can be passed with no limit: they
are provided as an array. Consequently, all of them must be of the same
type.
The last parameter in routine declaration must be marked as VARIADIC;
it must be of an array type.
We have already mentioned polymorphic routines that can accept
parameters of various types. Routine declaration uses a special
polymorphic pseudotype, while the actual type is defined at run time based
on the type of the passed parameters.
There is a separate polymorphic type anyarray (and anynonarray for
non-arrays).
This type can be used when passing a variable number of arguments via
a VARIADIC parameter.
9
An Array or a Table?
1 ... {A}
2 ... {B,C,D}
3 ... {A,C}
1 ...
2 ...
3 ...
1 1
2 2
2 3
2 4
3 1
3 3
a concise view
does not require joins
convenient in simple cases
separate tables:
many-to-many relationship
a universal solution
1 A ...
2 B ...
3 C ...
4 D ...
A traditional relational approach assumes that a table stores atomic values
(first normal form). The SQL language has no tools for peeking into
composite values.
That’s why a traditional approach consists in creating an additional table
connected to the main one by a many-to-many relationship.
Nevertheless, we can create a table with a column of an array type.
PostgreSQL offers a rich set of array functions; the search for an array
element can be sped up using special indexes (covered in the DEV2
course).
This approach can be convenient: we get a concise view that does not
require any joins. For example, arrays are extensively used in PostgreSQL
system catalog.
Which approach to choose depends on the goals and the operations
required. Take a look at the example in the demo.
11
Summary
An array consists of numbered elements of the same data type
A column with arrays is an alternative to a separate table:
it offers convenient operations on arrays and index support
Arrays enable you to create functions with a variable number
of arguments
12
Practice
1. Create an add_book function for adding a new book.
The function must take two parameters: the name of the book
and an array of author IDs. It must return the ID of the added
book.
Check that the application now allows adding books.
Task 1.
FUNCTION add_book(title text, authors integer[])
RETURNS integer
13
Practice
1. Implement a map function that takes two parameters:
array A of real numbers and the name of function F that takes a
single parameter of a real type.
The map function must apply function F to each element of
array A and return the result.
2. Implement a reduce function that takes two parameters:
an array of real numbers and the name of an auxiliary function
that takes two parameters of a real type.
The function must return a real number calculated by
sequentially folding the array from left to right.
3. Make map and reduce functions polymorphic.
Task 1. For example:
map(ARRAY[4.0,9.0],'sqrt') → ARRAY[2.0,3.0]
Task 2. For example:
reduce(ARRAY[1.0,3.0,2.0,0.5],'greatest') → 3.0
In this case, the value is calculated as follows:
greatest( greatest( greatest(1.0,3.0), 2.0 ), 0.5 )
