SQL
Composite Types
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
Composite Types and How to Use Them
Composite Type Arguments
Functions Returning a Single Row
Functions Returning a Set of Rows
3
Composite Types
Composite type
set of named attributes (fields)
same as a table row, but without constraints
Creating a composite type
explicit declaration of a new type
implicitly when a table is created
record: a placeholder composite type
Using a composite type
attributes as scalar values
operations on composite type values: comparison, check for NULL,
use with subqueries
A composite type represents a set of attributes, with each attribute having its
own name and type. A composite type is similar to a table row in many
ways. It is often called a record (similar to a stru ct ure in C-like languages).
A composite type is a database object. When it is declared, a new type is
registered in the system catalog, making it a full-fledged SQL type. A table
creation automatically produces a composite type with the same name. This
type represents a row of the table. An important difference is that composite
types do not have constraints.
Composite type attributes can be used as regular scalar values (although
each attribute can also be of a composite type itself).
A composite type can be used just like any other SQL type; for example,
you can create table columns of this type. Composite values can be
compared, checked for NULL, used with subqueries in clauses like IN,
ANY/SOME, ALL.
5
Routine Arguments
A routine can take composite type arguments
Implementing computed fields
table.column and column(table) are interchangeable
Other options
views
GENERATED ALWAYS columns
Naturally, routines can take arguments of composite types.
It’s worth noting that apart from the usual table.column notation, you can
access a table column using the functional form: column(table). It allows
us to create computed fields by declaring a function that takes a composite
value as an argument.
This approach is a bit odd because there is a more straightforward way to
get the same outcome by using a view. The SQL standard also defines
GENERATED ALWAYS columns, but their implementation in PostgreSQL
does not fully comply with the standard yet: generated columns are
STORED in a table instead of being generated on the fly. Such VIRTUAL
columns are to be introduced in PostgreSQL 18.
7
Single Row Functions
Return a composite value
Usually called in SELECT list
When called within a FROM clause, return a one-row table
Functions can both take arguments of a composite type and return
composite type values.
Functions are usually called in SELECT lists, but it is possible to call a
function within a FROM clause, as if it were a one-row table.
9
Set Returning Functions
Declared as RETURNS SETOF or RETURNS TABLE
Can return multiple rows
Usually called in a FROM clause
Can be used as a view with arguments
very convenient when combined with function inlining
We have tried calling functions in a FROM clause, but have only seen one-
row outputs so far. However, there is nothing stopping us from declaring
functions that would return a set of rows: the so-called table functions or set
returning functions (SRF).
It's only natural to call these functions in a FROM clause, turning them into a
pseudo-views to some extent. (Technically, PostgreSQL allows calling such
functions in SELECT lists as well, but it is not recommended.)
Like with regular functions, the planner can sometimes inline the function
body into the main query. It allows creating “views with arguments” without
additional overhead.
11
Takeaways
Composite types combine values of other types
Simplify and enrich function operations on tables
Implement computed fields and views with parameters
Functions can return multiple rows
12
Practice
1. Create a function onhand_qty to calculate books in stock.
The function takes a composite type parameter (books) and
returns an integer number.
Use this function in the catalog_v view as a computed field.
Verify that the application can now display the number of books.
2. Create a set returning function get_catalog for book search.
The function takes values from the search fields (author, book
title, in stock) and returns matching books in the catalog_v
format.
Verify that you can now search for books and browse in the Store
tab.
1.
FUNCTION onhand_qty(book books) RETURNS integer
2.
FUNCTION get_catalog(
author_name text, book_title text, in_stock boolean
)
RETURNS TABLE(
book_id integer, display_name text, onhand_qty integer
)
The obvious solution is to use the existing catalog_v view, just with some
row filters. But this view displays book titles and authors in the same field,
and authors’ names are abbreviated. Clearly, searching for “Reuel” in the
“J. R. R. Tolkien” field will yield no results.
The get_catalog function could repeat the query from the catalog_v view, but
it is code duplication, which is a bad practice. So you should extend the
catalog_v view by adding the following fields: the book title and the full list of
authors.
Verify that the empty fields in the form are handled correctly. When calling
the get_catalog function, does the client pass empty strings or null values?
13
Practice+
1. Create a function that converts a string representation of a
hexadecimal number into a regular integer number.
For example: convert('FF') → 255
2. Extend this function with an optional argument that defines the
base of a numeral system (16 by default).
For example: convert('0110',2) → 6
3. The generate_series set returning function does not work with
string types. Create your own function that generates string
sequences of uppercase Latin letters.
1. For example:
convert('FF') → 255
To solve this problem, you can use a regexp_split_to_table set returning
function, upper and reverse functions, and WITH ORDINALITY clause.
Another option is to use a recursive query.
You can check the implementation using hexadecimal constants:
SELECT X'FF'::integer;
2. For example:
convert('0110',2) → 6
3. Assume that the input strings have the same length. For example:
generate_series('AA','ZZ') →
→ 'AA'
'AB'
'AC'
...
'ZY'
'ZZ'
