SQL
Composite Types
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
Composite types and how to work with them
Composite type parameters
Functions returning a single row
Functions returning a set of rows
3
Composite Types
A composite type
a set of named attributes (fields)
the same as a table row, but without constraints
Creation
an explicit declaration of a new type
implicit creation together with a table
the record type, which serves as a placeholder for composite types
Usage
attributes as scalar values
operations on composite type values: comparison, check for NULL,
usage in subqueries
A composite type represents a set of attributes, with each attribute having
its own name and type. A composite type can be compared to a table row.
It is often called a “record” (or a “structure” 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 the 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, in its turn, can also be of a composite type).
A composite type can be used just like any other SQL type; for example,
you can create table columns of this type, etc. Composite values can be
compared, checked for NULL, used in subqueries in clauses like IN,
ANY/SOME, ALL.
5
Composite Type Parameters
A function can take parameters of composite types
Implementing computed fields using functions
table.column and column(table) are interchangeable
Other ways to set up computed fields
views
GENERATED ALWAYS columns
Naturally, functions can take parameters of composite types.
It’s worth noting that apart from the usual table.column notation, you
can access a table column using the following functional form:
column(table). It allows us to create computed fields by declaring
a function that takes a composite value as an input parameter.
This approach is a bit odd because there is a more straightforward way
to get the same outcome using a view. The SQL standard also defines
GENERATED ALWAYS columns, but their implementation in PostgreSQL
does not fully comply with the standard yet: such columns are stored in
a table instead of being generated on the fly.
7
One-Row Functions
Return a composite value
Are usually called in the context of SELECT lists
When called in the FROM clause, return a one-row table
Functions can both take parameters of a composite type and return
composite type values.
Functions are usually called in the context of SELECT lists.
But it is also possible to call a function in the FROM clause, as if it were
a one-row table.
9
Set-Returning Functions
Are declared as RETURNS SETOF or RETURNS TABLE
Can return several rows
Are usually called in the FROM clause
Can be used as a view with parameters
it is especially convenient when combined with function inlining
As we know, functions can be called in the FROM clause, but we have only
seen one-row results so far. It is certainly more interesting to have functions
that return sets of rows, and we can declare them as well.
It is natural to call set-returning functions in the FROM clause; they can be
considered as some kind of a view. (Formally, PostgreSQL allows calling
such functions in SELECT lists as well, but it is not recommended.)
Like with regular functions, the planner can sometimes perform inlining, i.e.,
insert the function body into the main query. It allows creating “views with
parameters” without additional overhead.
11
Summary
Composite types combine values of other types
Simplify and enrich function operations on tables
Implement computed fields and views with parameters
12
Practice
1. Create a function onhand_qty to calculate books in stock.
The function takes a parameter of a composite type (books)
and returns an integer number.
Use this function in the catalog_v view as a computed field.
Check 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.
Check that you can now browse and search for books in “Store.”
Task 1.
FUNCTION onhand_qty(book books) RETURNS integer
Task 2.
FUNCTION get_catalog(
author_name text, book_title text, in_stock boolean
)
RETURNS TABLE(
book_id integer, display_name text, onhand_qty integer
)
To solve this problem, it is tempting to use the already available catalog_v
view and simply filter out some rows. But this view displays book titles and
authors in the same field, and authors’ middle names are abbreviated. It is
clear that the search for “Klapka” in the “Jerome K. Jerome” field will not
return any results.
The get_catalog function could repeat the query from the catalog_v
view, but it is code duplication, which is bad practice. So you should extend
the catalog_v view by adding the following fields: the book title and the full
list of authors.
Check 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.
2. Extend this function with an optional parameter that defines
the base of a numeral system (16 by default).
3. The set-returning function generate_series does not support
text types. Create your own function that generates string
sequences of uppercase Latin letters.
Task 1. For example:
convert('FF') → 255
To solve this problem, you can use a set-returning function
regexp_split_to_table, functions upper and reverse, and WITH
ORDINALITY clause.
Another option is to use a recursive query.
You can check the implementation using hexadecimal constants:
SELECT X'FF'::integer;
Task 2. For example:
convert('0110',2) → 6
Task 3. Assume that the input strings have the same length. For example:
generate_series('AA','ZZ') →
→ 'AA'
'AB'
'AC'
...
'ZY'
'ZZ'
