Расширяемость
Пользовательские типы данных
16
Авторские права
© Postgres Professional, 2017–2024
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Составные типы (краткое повторение)
Типы перечислений
Диапазонные типы и мультидиапазоны
Базовые типы
Домены
Приведение типов, операторы
3
Расширяемость типов
Богатый набор существующих типов
числа, строки, даты, слабоструктурированные, геометрия, ...
массивы
Средства для создания новых типов
простые: на основе уже имеющихся типов
сложные: новый тип «с нуля»
PostgreSQL имеет множество встроенных типов данных (и поддержку
массивов элементов любых типов данных).
Вместе с тем имеются и средства для создания своих собственных
типов данных.
В простом случае новые типы создаются по одной из предопределен-
ных схем из других, уже имеющихся, типов. Это составные типы,
перечисления, диапазоны, домены.
Если же необходимый тип данных не может быть выражен с помощью
этих простых средств, можно создать новый тип «с нуля», детально
описав его поведение на низком уровне.
4
Составные типы
Набор именованных атрибутов (табличная строка)
Создание нового типа
вручную: CREATE TYPE AS
автоматически при создании отношения
Составные типы представляют собой набор именованных атрибутов
каких-либо других типов данных. В разных языках программирования
такие типы могут называться структурами или записями.
По сути, составной тип описывает табличную строку и поэтому
создается автоматически при создании любой таблицы или
представления.
Новый составной тип можно создать и вручную.
Составные типы определены в стандарте SQL. Они подробно
рассматриваются в курсе DEV1, поэтому здесь мы не будем на них
останавливаться.
5
Типы перечислений
Упорядоченный набор значений
значения выглядят как текстовые строки
хранятся как 4-байтовые числа (oid)
Создание нового типа
только вручную: CREATE TYPE AS ENUM
Тип перечисления создается простым перечислением всех допустимых
значений.
Значения задаются и используются в виде текстовых строк, которые
хранятся в системном каталоге как упорядоченный набор значений.
При использовании перечисления, например, в качестве типа столбца
таблицы, каждое его значение будет представлено в виде 4-байтного
целого числа (фактически, тип oid). Это позволяет использовать
длинные (до 63 символов) описательные значения, не экономя
на месте в таблице.
Стандартных типов перечисления в PostgreSQL нет.
7
Диапазонные типы
Непрерывный отрезок значений от начальной до конечной
точки
открытый (>, <), закрытый (≤, ≥), неограниченный
Создание нового типа
CREATE TYPE AS RANGE
уже есть для integer, bigint, numeric, timestamp, date
Операции
сравнение
проверка на включение диапазона или элемента, на пересечение
объединение, пересечение, вычитание
и др.
Диапазонный тип представляет непрерывный отрезок значений
скалярного типа. Концы отрезка могут быть открытыми (граничное
значение не включается) или закрытыми (наоборот, включается), или
отрезок может быть бесконечным, то есть не иметь одной или обеих
границ.
Для диапазонов определено довольно много удобных операций,
в том числе:
●
проверки на включение одного диапазона в другой, на
принадлежность значения диапазона, на пересечение двух
диапазонов;
●
обычные операции над множествами: объединение, пересечение,
вычитание.
Диапазоны предопределены для различных числовых типов и дат
(например, int4range для integer и др.). Можно создать и свой
собственный диапазонный тип.
8
Мультидиапазоны
Множество диапазонов
не должны пересекаться
Создание нового типа
автоматически при создании диапазона
Операции
сравнение
проверка на включение мультидиапазона, диапазона или элемента
проверка на пересечение
объединение, пересечение, вычитание
и др.
Для каждого диапазонного типа определен соответствующий
мультидиапазонный тип. Такой тип по сути представляет собой массив
отдельных диапазонов. Диапазоны внутри мультидиапазонного типа
не должны пересекаться между собой, но между диапазонами могут
быть пропуски. Большинство диапазонных операторов работают
и с мультидиапазонами. Кроме того, есть функции для работы именно
с мультидиапазонными типами.
Мультидиапазонный тип определяется автоматически при определении
диапазонного типа. По умолчанию имя мультидиапазонного типа
генерируется PostgreSQL, но можно задать его и явно.
10
Интервалы дат и времени
Интервал значений определенной длины
Операции
арифметика дат и времени
функции корректировки
В PostgreSQL есть тип interval, на первый взгляд похожий на диапазон.
Но, в отличие от диапазонных типов, интервал определяется длиной,
а не начальной и конечной точкой. Кроме того, интервалы существуют
только для дат и времени.
Отметим также, что при работе с этим типом есть ряд тонкостей,
обусловленных особенностями хранения значений интервалов.
Например, существуют специальные функции для корректировки числа
дней и часов при их выходе за обычные границы (одну из них мы
увидим в демонстрации).
Создание собственных типов, аналогичных interval, невозможно. Здесь
мы показываем этот тип только для полноты картины и не будем
останавливаться на нем подробно.
12
Домены
Ограничение допустимых значений существующего типа
ограничение NOT NULL
значение по умолчанию DEFAULT
проверка CHECK
Создание нового типа
только вручную: CREATE DOMAIN
Домен создается на базе любого существующего типа данных и служит
для ограничения множества допустимых значений этого типа.
Определяя домен, можно запретить неопределенные значения
(и определить значение по умолчанию), можно также указать
произвольную проверку допустимости значения.
По сути, это похоже на ограничения целостности, накладываемые
на столбцы при создании таблицы. Создав тип домена, его можно
использовать в нескольких таблицах, не указывая каждый раз одни
и те же ограничения.
Домены определены в стандарте SQL.
14
Базовые типы
Тип, не сводящийся к комбинации существующих
Создание нового типа
CREATE TYPE, требует программирования на языке C
множество типов предоставляются расширениями
Иногда может возникнуть потребность в совершенно новом типе,
который нельзя представить как комбинацию уже существующих.
В этом случае потребуется определить все низкоуровневые детали
внутреннего устройства типа, что можно сделать только на языке C.
Поэтому мы не будем рассматривать эту возможность.
Впрочем, множество различных типов данных доступны в виде
расширений (в том числе сторонних), так что вероятность того, что
потребуется совершенно новый тип, очень мала.
15
Действия над типами
Функции, принимающие или возвращающие значения типа
Приведение типов
преобразование значения одного типа к другому типу
Операторы
унарные (префиксные)
бинарные (инфиксные)
Понятно, что мало определить новый тип данных — надо уметь его как-
то использовать.
Написанию функций, принимающих или возвращающих значения
произвольных (в том числе определенных пользователем) типов
посвящен курс DEV1, поэтому на этом мы не будем специально
останавливаться.
На функциях основаны и другие важные объекты — приведения типов
и операторы, — которые могут определяться пользователем.
Приведения типов позволяют преобразовывать значения одного типа
к другому.
Операторы позволяют вместо вызова функции f(x,y) использовать
инфиксную нотацию, например x+y, что позволяет сделать код проще
и нагляднее. Кроме того, как мы увидим в следующей теме «Классы
операторов», операторы играют важную роль в индексировании.
Приведение типов в документации:
Операторы:
17
Итоги
PostgreSQL позволяет создавать новые типы данных
составные, перечисления, диапазоны и мультидиапазоны, домены
базовые типы
Для работы со значениями типов могут создаваться
функции, операторы и приведения типов
18
Практика
1. Реализуйте в приложении возможность установки розничной
цены книг с указанной пользователем даты (сейчас дата
просто игнорируется).
Предыдущая история изменений цен должна сохраняться.
Для этого добавьте в таблицу retail_prices информацию
о периоде действия цены и внесите необходимые изменения
в функции get_retail_price и set_retail_price.
2. Создайте составной тип для формата издания, состоящего из
размеров типографского листа и доли листа. Также создайте
приведение для этого нового типа к текстовому.
Замените тип данных столбца format таблицы books
и убедитесь, что интерфейс с приложением не изменился.
1. Интервал действия розничной цены можно представить по-разному:
●
двумя столбцами типа timestamptz («дата с» и «дата по»);
●
одним столбцом типа timestamptz («дата с»), при этом цена считается
действующей до следующей даты в другой строке таблицы;
●
одним столбцом диапазонного типа tstzrange.
Реализуйте последний вариант. Обратите внимание:
●
В функции set_retail_price придется изменять две строки таблицы,
и это должно корректно работать в случае, если несколько
пользователей устанавливают цену одновременно.
●
Функция get_retail_price должна получать цену на текущий момент.
Используйте функцию current_timestamp (возвращающую время
начала транзакции), а не clock_timestamp. Это будет важно при
последующих изменениях.
2. Формат издания записывается в виде WxH/N, где W и H — ширина
и высота типографского листа, с которым работает печатная машина,
и который разрезается потом на N одинаковых частей (страниц книги).
Поэтому N как правило представляет собой степень двойки.
Несмотря на то, что отношение длины страницы к ширине может быть
разным, форматы книг можно упорядочить, сравнивая площади
страниц, которые равны W*H/N.
При замене типа столбца обратите внимание на блокировку, которая
при этом удерживается.
19
Практика+
1. Производственные смены каждого сотрудника фабрики
хранятся в таблице. Напишите запрос, который покажет,
сколько человеко-часов пропадет даром, если в некоторый
момент времени на 15 минут будет отключено
электричество.
2. В таблице, содержащей много данных, используется столбец
статуса, имеющий тип перечисления. Проверьте, какие
блокировки будут установлены на таблицу при
необходимости добавления нового статуса, и какое время
занимает эта операция.
Сравните с альтернативным подходом, при котором
перечисление реализуется как обычный текстовый тип
с ограничением CHECK на уровне столбца или домена.
1. Производственные смены могут храниться в таблице вида:
CREATE TABLE shifts (
employee_name text,
work_hours tstzrange
);
2. Блокировки можно посмотреть в таблице pg_locks, как говорилось
в теме «Обзор блокировок».
Кроме обычной проверки
status text CHECK (status IN (...))
рассмотрите также вариант с использованием домена.
