SQL
Составные типы
16
Авторские права
© Postgres Professional, 2017–2024
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Составные типы и работа с ними
Параметры функций составных типов
Функции, возвращающие одну строку
Функции, возвращающие множество строк
3
Составные типы
Составной тип
набор именованных атрибутов (полей)
то же, что табличная строка, но без ограничений целостности
Создание
явное объявление нового типа
неявно при создании таблицы
неопределенный составной тип record
Использование
атрибуты как скалярные значения
операции со значениями составного типа: сравнение, проверка
на NULL, использование с подзапросами
Составной тип — это набор атрибутов, каждый из которых имеет свое
имя и свой тип. Составной тип можно рассматривать как табличную
строку. Часто он называется «записью» (а в Си-подобных языках такой
тип называется «структурой»).
Составной тип — объект базы данных, его объявление регистрирует
новый тип в системном каталоге, после чего он становится
полноценным типом SQL. При создании таблицы автоматически
создается и одноименный составной тип, представляющий строку этой
таблицы. Важное отличие состоит в том, что в составном типе нет
ограничений целостности.
Атрибуты составного типа могут использоваться как отдельные
значения (хотя атрибут, в свою очередь, тоже может иметь составной
тип).
Составной тип можно использовать как любой другой тип SQL,
например, создавать столбцы таблиц этого типа и т. п. Значения
составного типа можно сравнивать между собой, проверять
на неопределенность (NULL), использовать с подзапросами в таких
конструкциях, как IN, ANY/SOME, ALL.
5
Параметры подпрограмм
Подпрограмма может принимать параметры составного
типа
Способ реализации вычисляемых полей
взаимозаменяемость table.column и column(table)
Другие способы
представления
столбцы GENERATED ALWAYS
Разумеется, подпрограммы могут принимать параметры составных
типов.
Интересно, что для доступа к столбцу таблицы можно использовать
не только привычную форму «таблица.столбец», но и функциональную:
«столбец(таблица)». Это позволяет создавать вычисляемые поля,
определяя функцию, принимающую на вход составной тип.
Это несколько курьезный способ, поскольку того же результата можно
добиться более явно с помощью представления. Стандарт SQL также
предусматривает генерируемые (GENERATED ALWAYS) столбцы, хотя
в PostgreSQL эта возможность пока реализована не в соответствии
со стандартом — столбцы не вычисляются на лету, а сохраняются
в таблице.
7
«Однострочные» функции
Возвращают значение составного типа
Обычно вызываются в списке выборки запроса
При вызове в предложении FROM возвращают
однострочную таблицу
Функции могут не только принимать параметры составного типа,
но и возвращать значение составного типа.
Обычно функции вызываются в списке выборки запроса (предложение
SELECT).
Но функцию можно вызвать и в предложении FROM, как будто таблицу
из одной строки.
9
Табличные функции
Объявляются как RETURNS SETOF или RETURNS TABLE
Могут возвращать несколько строк
Обычно вызываются в предложении FROM
Можно использовать как представление с параметрами
особенно удобно в сочетании с подстановкой тела функции в запрос
Мы знаем, что к функциям можно обращаться в предложении FROM,
однако до сих пор результатом была одна строка. Конечно, интереснее
было бы иметь функции, возвращающие множество строк (табличные
функции) — и их действительно можно определять.
Табличные функции естественно вызывать в предложении FROM
и можно рассматривать их как своеобразное представление.
(Формально PostgreSQL позволяет вызвать такую функцию и в списке
выборки, но так лучше не делать.)
Как и с обычными функциями, в ряде случаев планировщик может
подставить тело функции в основной запрос. Это позволяет создавать
«представления с параметрами» без накладных расходов.
11
Итоги
Составной тип объединяет значения других типов
Упрощает и обогащает работу функций с таблицами
Позволяет создавать вычислимые поля
и представления с параметрами
Функции могут возвращать множество строк
12
Практика
1. Создайте функцию onhand_qty для подсчета имеющихся
в наличии книг. Функция принимает параметр составного
типа books и возвращает целое число.
Используйте эту функцию в представлении catalog_v
в качестве «вычисляемого поля».
Проверьте, что приложение отображает количество книг.
2. Создайте табличную функцию get_catalog для поиска книг.
Функция принимает значения полей формы поиска
(«имя автора», «название книги», «есть на складе»)
и возвращает подходящие книги в формате catalog_v.
Проверьте, что в «Магазине» начал работать поиск и
просмотр.
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
)
При решении хотелось бы воспользоваться уже готовым
представлением catalog_v, просто наложив ограничения на строки.
Но в этом представлении и название книги, и авторы находятся в одном
поле, к тому же в сокращенном виде. Очевидно, что поиск автора «Лев»
по полю «Л .Н. Толстой» не даст результата.
Можно было бы повторить в функции get_catalog запрос из catalog_v,
но это дублирование кода, что плохо. Поэтому расширьте
представление catalog_v, добавив в него дополнительные поля:
заголовок книги и полный список авторов.
Проверьте, что корректно обрабатываются пустые поля на форме.
Когда клиент вызывает функцию get_catalog, передает ли он в этом
случае пустые строки или неопределенные значения?
13
Практика+
1. Напишите функцию, переводящую строку, содержащую
число в шестнадцатеричной системе, в обычное целое число.
Например: convert('FF') → 255
2. Добавьте в функцию второй необязательный параметр —
основание системы счисления (по умолчанию — 16).
Например: convert('0110',2) → 6
3. Табличная функция generate_series не работает
со строковыми типами. Предложите свою функцию
для генерации последовательностей строк из заглавных
английских букв.
1. Например:
convert('FF') → 255
Для решения пригодятся: табличная функция regexp_split_to_table,
функции upper и reverse, конструкция WITH ORDINALITY.
Другое решение возможно с помощью рекурсивного запроса.
Проверить реализацию можно, используя шестнадцатеричные
константы: SELECT X'FF'::integer;
2. Например:
convert('0110',2) → 6
3. Считайте, что на вход подаются строки равной длины. Например:
generate_series('AA','ZZ') →
→ 'AA'
'AB'
'AC'
...
'ZY'
'ZZ'
