SQL
Composite Types
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
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).
https://postgrespro.com/docs/postgresql/17/rowtypes
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 the row of the table. An important difference is that
composite types do not have constraints.
https://postgrespro.com/docs/postgresql/17/sql-createtype
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.
https://postgrespro.com/docs/postgresql/17/functions-comparisons
https://postgrespro.com/docs/postgresql/17/functions-subquery
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.
https://postgrespro.com/docs/postgresql/17/xfunc-sql
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.
https://postgrespro.com/docs/postgresql/17/ddl-generated-columns
7
Functions returning one row
Return a composite value
Usually called in SELECT lists
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 whole sets 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.
https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions
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 table 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 browse and search for books 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 view catalog_v, 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?