PL/pgSQL
Arrays
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
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 types
Array
a set of numbered elements of the same type
one-dimensional, multidimensional
Initialization
usage without an explicit declaration (type_name[])
implicit declaration when creating a base type or a table (_type_name)
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. When any base type
or a table is created, a new array type is also declared. Its name is the same
as the original type name, but with an underscore in front: _type_name. To
declare an array variable, all you need is to append square brackets to the
name of the element type.
An array is a full-fledged SQL type: you can create table columns of this
type, pass arrays as function arguments, and so on. Array elements can be
used 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.
https://postgrespro.com/docs/postgresql/17/arrays
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).
https://postgrespro.com/docs/postgresql/17/plpgsql-control-structures#PLP
GSQL-FOREACH-ARRAY
7
Arrays and routines
Routines with a variable number of arguments
all optional arguments must be of the same type
optional arguments are passed to the routine as an array
the last parameter array is defined by the VARIADIC mode
Polymorphic routines
support arguments of various types;
the actual type is defined at run time
can use additional polymorphic pseudotypes anyarray, anynonarray,
anycompatiblearray and anycompatiblenonarrray
can have a variable number of arguments
Using arrays, you can create routines (functions and procedures) with a
variable number of arguments.
While parameters with default values have to be explicitly specified in
routine declaration, optional arguments can be passed with no limit: they are
provided as an array. Consequently, all of them must be of the same type
(or a compatible one, if anycompatible or anycompatiblearray is used).
The last parameter in routine declaration must be marked as VARIADIC; it
must be of an array type.
https://postgrespro.com/docs/postgresql/17/xfunc-sql#XFUNC-SQL-VARIAD
IC-FUNCTIONS
We have already mentioned polymorphic routines that can accept
arguments of various types. Routine declaration uses a special polymorphic
pseudotype, while the actual type is defined at run time based on the types
of the passed arguments.
There are special polymorphic types anyarray and anycompatiblearray (and
anynonarray and anycompatiblenonarrray for non-arrays).
These types can be used when passing a variable number of arguments via
a VARIADIC parameter.
https://postgrespro.com/docs/postgresql/17/xfunc-sql#XFUNC-SQL-POLYM
ORPHIC-FUNCTIONS
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
compact representation
no joins required
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 relies on 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 representation that
does not require any joins. For example, arrays are extensively used in
PostgreSQL system catalog.
The choice of approach depends on the goals and the operations required.
Consider the example in the demo.
11
Takeaways
An array consists of numbered elements of the same data type
An array column 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 a function add_book for adding a new book.
The function must take two arguments: 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.
1.
FUNCTION add_book(title text, authors integer[])
RETURNS integer