# Занятие 09: Расширяемость / Пользовательские типы данных > Исходная страница в нативном Markdown: Расширяемость / Пользовательские типы данных --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [dev2_09_ext_datatypes.html](https://edu.postgrespro.ru/16/dev2-16/dev2_09_ext_datatypes.html) > **Archive:** [DEV2-handouts-16.zip](https://edu.postgrespro.ru/16/DEV2-handouts-16.zip) > **Страниц PDF:** 29 ## Page 1: Расширяемость / Пользовательские типы данных {#source-page-001} ### Авторские права © Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Составные типы (краткое повторение) - Типы перечислений - Диапазонные типы и мультидиапазоны - Базовые типы - Домены - Приведение типов, операторы ## Page 3: Расширяемость типов {#source-page-003} **Богатый набор существующих типов** - числа, строки, даты, слабоструктурированные, геометрия, ... - массивы - Средства для создания новых типов - простые: на основе уже имеющихся типов - сложные: новый тип «с нуля» ### Комментарии преподавателя PostgreSQL имеет множество встроенных типов данных (и поддержку массивов элементов любых типов данных). Вместе с тем имеются и средства для создания своих собственных типов данных. В простом случае новые типы создаются по одной из предопределен-ных схем из других, уже имеющихся, типов. Это составные типы, перечисления, диапазоны, домены. Если же необходимый тип данных не может быть выражен с помощью этих простых средств, можно создать новый тип «с нуля», детально описав его поведение на низком уровне. ## Page 4: Составные типы {#source-page-004} **Набор именованных атрибутов (табличная строка)** - Создание нового типа - вручную: CREATE TYPE AS - автоматически при создании отношения ### Комментарии преподавателя Составные типы представляют собой набор именованных атрибутов каких-либо других типов данных. В разных языках программирования такие типы могут называться структурами или записями. По сути, составной тип описывает табличную строку и поэтому создается автоматически при создании любой таблицы или представления. Новый составной тип можно создать и вручную. Составные типы определены в стандарте SQL. Они подробно рассматриваются в курсе DEV1, поэтому здесь мы не будем на них останавливаться. TE-TYPE-COMPARISON ## Page 5: Типы перечислений {#source-page-005} **Упорядоченный набор значений** - значения выглядят как текстовые строки - хранятся как 4-байтовые числа (oid) - Создание нового типа - только вручную: CREATE TYPE AS ENUM ### Комментарии преподавателя Тип перечисления создается простым перечислением всех допустимых значений. Значения задаются и используются в виде текстовых строк, которые хранятся в системном каталоге как упорядоченный набор значений. При использовании перечисления, например, в качестве типа столбца таблицы, каждое его значение будет представлено в виде 4-байтного целого числа (фактически, тип oid). Это позволяет использовать длинные (до 63 символов) описательные значения, не экономя на месте в таблице. Стандартных типов перечисления в PostgreSQL нет. ## Page 6: Типы перечислений {#source-page-006} ```sql => CREATE DATABASE ext_datatypes; ``` ```sql CREATE DATABASE ``` ```sql => \c ext_datatypes ``` ```text You are now connected to database "ext_datatypes" as user "student". ``` Создадим новые типы перечислений для старшинства карт: ```sql => CREATE TYPE ranks AS ENUM ( '6', '7', '8', '9', '10', 'Queen', 'King', 'Ace' ); ``` ```sql CREATE TYPE ``` И для мастей: ```sql => CREATE TYPE suits AS ENUM ( 'clubs', 'diamonds', 'hearts', 'spades' ); ``` ```sql CREATE TYPE ``` Напомним, что тип данных — отдельный объект базы данных, который хранится в системном каталоге: ```sql => \dT public.* ``` ```text List of data types Schema | Name | Description --------+-------+------------- public | ranks | public | suits | (2 rows) ``` Чтобы вспомнить и составные типы, определим еще тип для карты: ```sql => CREATE TYPE cards AS ( rank ranks, suit suits ); ``` ```sql CREATE TYPE ``` ```text => DO $$ DECLARE card cards; BEGIN card := ('Ace','spades'); RAISE NOTICE '%', card; RAISE NOTICE 'Старшинство: % ... %', enum_first(card.rank), enum_last(card.rank); RAISE NOTICE 'Масти: % ... %', enum_first(card.suit), enum_last(card.suit); END; $$; ``` ```text NOTICE: (Ace,spades) NOTICE: Старшинство: 6 ... Ace NOTICE: Масти: clubs ... spades DO ``` Обратите внимание, что названия значений перечислимого типа регистрозависимы. Все значения в перечислении упорядочены, и этим можно пользоваться: ```sql => SELECT '6'::ranks < 'Ace'::ranks; ``` ```text ?column? ---------- t (1 row) ``` Однако сами типы перечислений считаются уникальными и поэтому значения разных перечислений нельзя сравнивать, даже если они определены одинаково: ## Page 7: Page 7 {#source-page-007} ```sql => CREATE TYPE suits_bis AS ENUM ( 'clubs', 'diamonds', 'hearts', 'spades' ); ``` ```sql CREATE TYPE ``` ```sql => SELECT 'hearts'::suits = 'hearts'::suits_bis; ``` ```sql ERROR: operator does not exist: suits = suits_bis LINE 1: SELECT 'hearts'::suits = 'hearts'::suits_bis; ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. ``` При необходимости выполнить подобное сравнение придется либо реализовать собственный оператор, либо явно преобразовать типы в запросе, например так: ```sql => SELECT 'hearts'::suits::text = 'hearts'::suits_bis::text; ``` ```text ?column? ---------- t (1 row) ``` Можно получить список значений в виде массива (функция enum\_range смотрит только на тип параметра; само значение может быть любым): ```text => DO $$ DECLARE suit suits; rank ranks; deck cards[]; BEGIN RAISE NOTICE '%', enum_range(NULL::ranks); RAISE NOTICE '%', enum_range(NULL::suits); FOREACH rank IN ARRAY enum_range(NULL::ranks) LOOP FOREACH suit IN ARRAY enum_range(NULL::suits) LOOP deck := deck || (rank,suit)::cards; END LOOP; END LOOP; END; $$; ``` ```text NOTICE: {6,7,8,9,10,Queen,King,Ace} NOTICE: {clubs,diamonds,hearts,spades} DO ``` Кажется, мы забыли валета. Не беда, его можно добавить к перечислению: ```sql => ALTER TYPE ranks ADD VALUE 'Jack' BEFORE 'Queen'; ``` ```sql ALTER TYPE ``` А вот удалить значение из типа невозможно. Единственный вариант — удалить тип и создать его заново. Если мы заглянем в системный каталог, то увидим, что упорядоченность значений перечисления обеспечивает столбец типа real. Обратите внимание на его дробное значение для добавленного нами валета: ```sql => SELECT enumsortorder, enumlabel FROM pg_enum WHERE enumtypid = 'ranks'::regtype ORDER BY enumsortorder; ``` ```text enumsortorder | enumlabel ---------------+----------- 1 | 6 2 | 7 3 | 8 4 | 9 5 | 10 5.5 | Jack 6 | Queen 7 | King 8 | Ace (9 rows) ``` ## Page 8: Диапазонные типы {#source-page-008} **Непрерывный отрезок значений от начальной до конечной точки** - открытый (>, <), закрытый (≤, ≥), неограниченный - Создание нового типа - CREATE TYPE AS RANGE - уже есть для integer, bigint, numeric, timestamp, date - Операции - сравнение - проверка на включение диапазона или элемента, на пересечение - объединение, пересечение, вычитание - и др. ### Комментарии преподавателя Диапазонный тип представляет непрерывный отрезок значений скалярного типа. Концы отрезка могут быть открытыми (граничное значение не включается) или закрытыми (наоборот, включается), или отрезок может быть бесконечным, то есть не иметь одной или обеих границ. Для диапазонов определено довольно много удобных операций, в том числе: ● проверки на включение одного диапазона в другой, на принадлежность значения диапазона, на пересечение двух диапазонов; ● обычные операции над множествами: объединение, пересечение, вычитание. Диапазоны предопределены для различных числовых типов и дат (например, int4range для integer и др.). Можно создать и свой собственный диапазонный тип. ## Page 9: Мультидиапазоны {#source-page-009} **Множество диапазонов** - не должны пересекаться - Создание нового типа - автоматически при создании диапазона - Операции - сравнение - проверка на включение мультидиапазона, диапазона или элемента - проверка на пересечение - объединение, пересечение, вычитание - и др. ### Комментарии преподавателя Для каждого диапазонного типа определен соответствующий мультидиапазонный тип. Такой тип по сути представляет собой массив отдельных диапазонов. Диапазоны внутри мультидиапазонного типа не должны пересекаться между собой, но между диапазонами могут быть пропуски. Большинство диапазонных операторов работают и с мультидиапазонами. Кроме того, есть функции для работы именно с мультидиапазонными типами. Мультидиапазонный тип определяется автоматически при определении диапазонного типа. По умолчанию имя мультидиапазонного типа генерируется PostgreSQL, но можно задать его и явно. ## Page 10: Диапазонные типы {#source-page-010} Начнем с нескольких примеров работы с уже имеющимися диапазонными типами. Диапазон целых чисел от 1 до 9, обе границы включаются (об этом говорят квадратные скобки). Что будет выведено? ```sql => SELECT '[1,9]'::int4range; ``` ```text int4range ----------- [1,10) (1 row) ``` Результат выводится в каноническом виде, в котором левая граница включается, а правая — нет (круглая скобка). Конечно, значение правой границы при этом увеличивается на единицу. Канонический вид определен не для всех типов. Диапазон может быть и открытым. Проверим, принадлежит ли число диапазону: ```sql => SELECT -100 <@ '[1,)'::int4range, 100 <@ '[1,)'::int4range; ``` ```text ?column? | ?column? ----------+---------- f | t (1 row) ``` Точно так же можно проверить включение одного диапазона в другой: ```sql => SELECT '[2,3]'::int4range <@ '[1,4]'::int4range; ``` ```text ?column? ---------- t (1 row) ``` Удобны функции, позволяющие работать с диапазонами как с множествами. Например, можно легко найти пересечение (общие точки) двух диапазонов: ```sql => SELECT '[1,3]'::int4range * '[2,4]'::int4range; ``` ```text ?column? ---------- [2,4) (1 row) ``` При создании нового диапазонного типа указываются: базовый тип, значения которого должны быть сортируемыми; функция разности двух значений, которая должна возвращать результат типа double precision. Например, в PostgreSQL нет типа для диапазонов времени (без даты). Чтобы определить его, нам потребуется функция, возвращающая разность между двумя временами. Обычная разность возвращает значение типа interval: ```sql => SELECT '10:00'::time - '9:59'::time; ``` ```text ?column? ---------- 00:01:00 (1 row) ``` Но мы можем воспользоваться функцией extract epoch, которая вернет длину диапазона в секундах: ```sql => CREATE FUNCTION time_diff(a time, b time) RETURNS double precision LANGUAGE sql STRICT IMMUTABLE RETURN extract(epoch FROM (a - b)); ``` ```sql CREATE FUNCTION ``` ```sql => SELECT time_diff('10:00','9:59'); ``` ```text time_diff ----------- 60 (1 row) ``` ## Page 11: Page 11 {#source-page-011} Теперь можно определить тип диапазона, а вместе с ним — и мультидиапазона: ```sql => CREATE TYPE timerange AS RANGE ( subtype = time, subtype_diff = time_diff, multirange_type_name = timemultirange ); ``` ```sql CREATE TYPE ``` Последний параметр — необязательный, в его отсутствие соответствующий мультидиапазонный тип будет создан автоматически. Новый тип можно использовать аналогично уже существующим. Например, найдем объединение (здесь показаны два способа записи констант диапазонного типа): ```sql => SELECT timerange('9:00','11:00','[]') + '[10:00,12:00]'::timerange; ``` ```text ?column? --------------------- [09:00:00,12:00:00] (1 row) ``` Заметьте, что диапазон не приводится к каноническому виду. Чтобы это работало, необходимо реализовать соответствующую функцию и указать ее в определении типа. Мы не будем этого делать. Новый мультидиапазонный тип позволяет представить несколько диапазонов как одно значение: ```sql => SELECT timemultirange( timerange('09:00', '13:00', '[)'), timerange('08:00', '10:00', '[)'), timerange('14:00', '18:00', '[)') ) AS working_hours; ``` ```text working_hours ------------------------------------------- {[08:00:00,13:00:00),[14:00:00,18:00:00)} (1 row) ``` Обратите внимание, что пересекающиеся диапазоны были объединены. ## Page 12: Интервалы дат и времени {#source-page-012} **Интервал значений определенной длины** - Операции - арифметика дат и времени - функции корректировки ### Комментарии преподавателя В PostgreSQL есть тип interval, на первый взгляд похожий на диапазон. Но, в отличие от диапазонных типов, интервал определяется длиной, а не начальной и конечной точкой. Кроме того, интервалы существуют только для дат и времени. Отметим также, что при работе с этим типом есть ряд тонкостей, обусловленных особенностями хранения значений интервалов. Например, существуют специальные функции для корректировки числа дней и часов при их выходе за обычные границы (одну из них мы увидим в демонстрации). Создание собственных типов, аналогичных interval, невозможно. Здесь мы показываем этот тип только для полноты картины и не будем останавливаться на нем подробно. ## Page 13: Интервалы {#source-page-013} Значения типа interval определяют длину временного отрезка, в отличие от диапазонного типа, значения которого состоят из начальной и конечной точек. Интервал появляется естественным образом при вычитании двух моментов времени: ```sql => SELECT timestamp '01.02.2020' - timestamp '01.01.2020'; ``` ```text ?column? ---------- 31 days (1 row) ``` (Хотя та же операция для дат (тип date) возвращает целое число.) Интервалы используются в арифметике дат и времени. Например: ```sql => SELECT now(), now() + 2 * interval '1 month'; ``` ```text now | ?column? -------------------------------+------------------------------- 2026-04-10 16:05:06.856995+03 | 2026-06-10 16:05:06.856995+03 (1 row) ``` Обратите внимание, что интервал '1 month' может иметь разную длину — от 28 до 31 дней в зависимости от месяца. При необходимости можно преобразовать интервал так, чтобы, например, каждый 24-часовой период считался одним днем: ```sql => SELECT interval '29 hours', justify_hours(interval '29 hours'); ``` ```text interval | justify_hours ----------+---------------- 29:00:00 | 1 day 05:00:00 (1 row) ``` ## Page 14: Домены {#source-page-014} **Ограничение допустимых значений существующего типа** - ограничение NOT NULL - значение по умолчанию DEFAULT - проверка CHECK - Создание нового типа - только вручную: CREATE DOMAIN ### Комментарии преподавателя Домен создается на базе любого существующего типа данных и служит для ограничения множества допустимых значений этого типа. Определяя домен, можно запретить неопределенные значения (и определить значение по умолчанию), можно также указать произвольную проверку допустимости значения. По сути, это похоже на ограничения целостности, накладываемые на столбцы при создании таблицы. Создав тип домена, его можно использовать в нескольких таблицах, не указывая каждый раз одни и те же ограничения. Домены определены в стандарте SQL. ## Page 15: Домены {#source-page-015} Создадим доменный тип, ограничивающий временной диапазон рабочими часами и запрещающий неопределенные значения: ```sql => CREATE DOMAIN work_timerange AS timerange NOT NULL CHECK (VALUE <@ '[10:00,19:00)'::timerange); ``` ```sql CREATE DOMAIN ``` Его можно использовать при создании таблицы: ```sql => CREATE TABLE work_events( event_name text, event_range work_timerange ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO work_events VALUES ( 'обед', '[13:00,14:00)' ); ``` ```sql INSERT 0 1 ``` А так получится? ```sql => INSERT INTO work_events VALUES ( 'труд', '[14:00,22:00)' ); ``` ```text ERROR: value for domain work_timerange violates check constraint "work_timerange_check" ``` Нет — нарушено условие проверки. А так? ```sql => INSERT INTO work_events VALUES ( 'лень', NULL ); ``` ```text ERROR: domain work_timerange does not allow null values ``` Нет — неопределенные значения не допускаются. А если так? ```sql => INSERT INTO work_events VALUES ( 'лень', (SELECT event_range FROM work_events WHERE false) ); ``` ```sql INSERT 0 1 ``` А так получится. Запрет неопределенных значений на уровне домена работает не так, как на уровне столбца. Ограничение NOT NULL надежнее указывать при создании таблицы. Еще одна проблема связана с тем, что при любых операциях значения приводятся к базовому типу. Это не позволяет использовать домены для контроля типов в операциях. Вот пример: ```sql => CREATE DOMAIN distance AS float; ``` ```sql CREATE DOMAIN ``` ```sql => CREATE DOMAIN weight AS float; ``` ```sql CREATE DOMAIN ``` Можно ли складывать метры с граммами? ```sql => SELECT 2::distance + 3::weight; ``` ```text ?column? ---------- 5 (1 row) ``` Оказывается, можно. Поэтому практическое применение доменных типов довольно ограничено. ## Page 16: Базовые типы {#source-page-016} **Тип, не сводящийся к комбинации существующих** - Создание нового типа - CREATE TYPE, требует программирования на языке C - множество типов предоставляются расширениями ### Комментарии преподавателя Иногда может возникнуть потребность в совершенно новом типе, который нельзя представить как комбинацию уже существующих. В этом случае потребуется определить все низкоуровневые детали внутреннего устройства типа, что можно сделать только на языке C. Поэтому мы не будем рассматривать эту возможность. Впрочем, множество различных типов данных доступны в виде расширений (в том числе сторонних), так что вероятность того, что потребуется совершенно новый тип, очень мала. ## Page 17: Действия над типами {#source-page-017} **Функции, принимающие или возвращающие значения типа** - Приведение типов - преобразование значения одного типа к другому типу - Операторы - унарные (префиксные) - бинарные (инфиксные) ### Комментарии преподавателя Понятно, что мало определить новый тип данных — надо уметь его как-то использовать. Написанию функций, принимающих или возвращающих значения произвольных (в том числе определенных пользователем) типов посвящен курс DEV1, поэтому на этом мы не будем специально останавливаться. На функциях основаны и другие важные объекты — приведения типов и операторы, — которые могут определяться пользователем. Приведения типов позволяют преобразовывать значения одного типа к другому. Операторы позволяют вместо вызова функции f(x,y) использовать инфиксную нотацию, например x+y, что позволяет сделать код проще и нагляднее. Кроме того, как мы увидим в следующей теме «Классы операторов», операторы играют важную роль в индексировании. Приведение типов в документации: Операторы: ## Page 18: Приведение типов {#source-page-018} В качестве примера рассмотрим приведение типа timerange к типу interval. Сработает ли такое приведение само по себе? ```sql => SELECT '[10:00,12:00]'::timerange::interval; ``` ```sql ERROR: cannot cast type timerange to interval LINE 1: SELECT '[10:00,12:00]'::timerange::interval; ^ ``` Увы, нет. Чтобы создать такое приведение, сначала напишем функцию: ```sql => CREATE FUNCTION timerange_to_interval(a timerange) RETURNS interval LANGUAGE sql STRICT IMMUTABLE RETURN make_interval( secs => extract( epoch FROM (upper(a)-lower(a)) ) ); ``` ```sql CREATE FUNCTION ``` ```sql => SELECT timerange_to_interval('[10:00,12:00]'); ``` ```text timerange_to_interval ----------------------- 02:00:00 (1 row) ``` Теперь можно создать приведение типов. Здесь мы указываем AS IMPLICIT, чтобы приведение срабатывало и неявно. ```sql => CREATE CAST (timerange AS interval) WITH FUNCTION timerange_to_interval AS IMPLICIT; ``` ```sql CREATE CAST ``` ```sql => SELECT '[10:00,12:00]'::timerange::interval; ``` ```text interval ---------- 02:00:00 (1 row) ``` ### Операторы Пусть требуется узнать, сколько раз заданный интервал (тип interval) содержится в другом интервале. Например, сколько в интервале часов, или минут, или пятисекундных отрезков. Иными словами, нужно деление одного интервала на другой. Но в PostgreSQL для интервалов определен только оператор деления на целое число. Напишем соответствующую функцию: ```sql => CREATE FUNCTION interval_div(a interval, b interval) RETURNS double precision LANGUAGE sql STRICT IMMUTABLE RETURN extract(epoch FROM a) / extract(epoch FROM b); ``` ```sql CREATE FUNCTION ``` Теперь мы можем узнать, сколько раз можно послушать композицию Your Latest Trick, длящуюся 6 минут 33 секунды, если у нас есть полтора часа времени: ```sql => SELECT interval_div('1:30:00'::interval,'0:06:33'::interval); ``` ```text interval_div -------------------- 13.740458015267176 (1 row) ``` Определим бинарный оператор, используя эту функцию: ```sql => CREATE OPERATOR / ( FUNCTION = interval_div, LEFTARG = interval, RIGHTARG = interval ); ``` ## Page 19: Page 19 {#source-page-019} ```sql CREATE OPERATOR ``` Имя оператора можно составлять только из специальных символов, список которых приведен в документации к команде CREATE OPERATOR. Если не указать LEFTARG, будет определен префиксный оператор. Постфиксные операторы не поддерживаются начиная с PostgreSQL 14. Теперь можно пользоваться удобной инфиксной формой записи (здесь также показан другой способ записи интервалов): ```sql => SELECT interval '1 hour 30 min' / interval '6 min 33 sec'; ``` ```text ?column? -------------------- 13.740458015267176 (1 row) ``` ## Page 20: Итоги {#source-page-020} **PostgreSQL позволяет создавать новые типы данных** - составные, перечисления, диапазоны и мультидиапазоны, домены - базовые типы - Для работы со значениями типов могут создаваться функции, операторы и приведения типов ## Page 21: Практика {#source-page-021} 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. При замене типа столбца обратите внимание на блокировку, которая при этом удерживается. ## Page 22: 1. Розничная цена на книги {#source-page-022} Добавим в таблицу retail\_prices столбец с диапазоном дат. Значение по умолчанию — неограниченный диапазон. ```sql => ALTER TABLE public.retail_prices ADD effective tstzrange NOT NULL DEFAULT '(,)'; ``` ```sql ALTER TABLE ``` Функция получения текущей цены должна выбрать тот диапазон, в который входит текущая дата: ```sql => CREATE OR REPLACE FUNCTION public.get_retail_price(book_id bigint) RETURNS numeric LANGUAGE sql STABLE SECURITY DEFINER RETURN (SELECT rp.price FROM retail_prices rp WHERE rp.book_id = get_retail_price.book_id AND rp.effective @> current_timestamp); ``` ```sql CREATE FUNCTION ``` Функция установки цены должна изменить одну строку и добавить одну новую. Использование уровня изоляции Read Committed в этом случае может приводить к аномалиям, поэтому сначала заблокируем соответствующую запись в таблице books, чтобы в один момент времени только одна транзакция могла устанавливать цену одной и той же книги. ```sql => CREATE OR REPLACE FUNCTION empapi.set_retail_price ( book_id bigint, price numeric, at timestamptz ) RETURNS void AS $$ DECLARE lower_bound timestamptz; upper_bound timestamptz; BEGIN PERFORM FROM books b WHERE b.book_id = set_retail_price.book_id FOR UPDATE; ``` ```sql SELECT lower(rp.effective), upper(rp.effective) INTO lower_bound, upper_bound FROM retail_prices rp WHERE rp.book_id = set_retail_price.book_id AND at <@ rp.effective; ``` ```sql IF at = lower_bound THEN -- только обновляем цену в существующем диапазоне UPDATE retail_prices rp SET price = set_retail_price.price WHERE rp.book_id = set_retail_price.book_id AND at <@ rp.effective ; ELSE -- закрываем существующий диапазон... UPDATE retail_prices rp SET effective = tstzrange(lower_bound, at, '[)') WHERE rp.book_id = set_retail_price.book_id AND at <@ rp.effective; -- ...и добавляем новый INSERT INTO retail_prices ( book_id, price, effective ) VALUES ( book_id, price, tstzrange(at, upper_bound, '[)') ); END IF; END; $$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER; ``` ```sql CREATE FUNCTION ``` Проверим. ```text => BEGIN; ``` ## Page 23: Page 23 {#source-page-023} ```text BEGIN ``` ```sql => SELECT * FROM retail_prices WHERE book_id = 1 ORDER BY effective; ``` ```text book_id | price | effective ---------+-------+----------- 1 | 640 | (,) (1 row) ``` Вставка новой цены в конец: ```sql => SELECT empapi.set_retail_price(1, 200.00, '2026-04-11 16:24:00+03'); ``` ```text set_retail_price ------------------ (1 row) ``` ```sql => SELECT * FROM retail_prices WHERE book_id = 1 ORDER BY effective; ``` ```text book_id | price | effective ---------+--------+----------------------------- 1 | 640 | (,"2026-04-11 16:24:00+03") 1 | 200.00 | ["2026-04-11 16:24:00+03",) (2 rows) ``` Вставка не в конец: ```sql => SELECT empapi.set_retail_price(1, 300.00, '2026-04-10 16:24:00+03'); ``` ```text set_retail_price ------------------ (1 row) ``` ```sql => SELECT * FROM retail_prices WHERE book_id = 1 ORDER BY effective; ``` ```text book_id | price | effective ---------+--------+----------------------------------------------------- 1 | 640 | (,"2026-04-10 16:24:00+03") 1 | 300.00 | ["2026-04-10 16:24:00+03","2026-04-11 16:24:00+03") 1 | 200.00 | ["2026-04-11 16:24:00+03",) (3 rows) ``` Цена с уже существующей датой (новый интервал не появляется): ```sql => SELECT empapi.set_retail_price(1, 400.00, '2026-04-10 16:24:00+03'); ``` ```text set_retail_price ------------------ (1 row) ``` ```sql => SELECT * FROM retail_prices WHERE book_id = 1 ORDER BY effective; ``` ```text book_id | price | effective ---------+--------+----------------------------------------------------- 1 | 640 | (,"2026-04-10 16:24:00+03") 1 | 400.00 | ["2026-04-10 16:24:00+03","2026-04-11 16:24:00+03") 1 | 200.00 | ["2026-04-11 16:24:00+03",) (3 rows) ``` ```text => ROLLBACK; ``` ```text ROLLBACK ``` ### 2. Тип данных для формата издания Тип данных: ```sql => CREATE TYPE book_format AS ( width integer, height integer, parts integer ); ``` ```sql CREATE TYPE ``` ## Page 24: Page 24 {#source-page-024} Преобразование в текст: ```sql => CREATE FUNCTION book_format_to_text(f book_format) RETURNS text LANGUAGE sql STRICT IMMUTABLE RETURN f.width || 'x' || f.height || '/' || f.parts; ``` ```sql CREATE FUNCTION ``` ```sql => CREATE CAST (book_format AS text) WITH FUNCTION book_format_to_text AS IMPLICIT; ``` ```sql CREATE CAST ``` Проверим: ```sql => SELECT (90,60,16)::book_format::text; ``` ```text row ---------- 90x60/16 (1 row) ``` И обратно: ```sql => CREATE FUNCTION text_to_book_format(f text) RETURNS book_format LANGUAGE sql STRICT IMMUTABLE RETURN (SELECT (m[1],m[2],m[3])::book_format FROM regexp_match(f, '(\d+)x(\d+)/(\d+)') m); ``` ```sql CREATE FUNCTION ``` ```sql => CREATE CAST (text AS book_format) WITH FUNCTION text_to_book_format AS IMPLICIT; ``` ```sql CREATE CAST ``` Проверим: ```sql => SELECT '90x60/16'::text::book_format; ``` ```text book_format ------------- (90,60,16) (1 row) ``` Обратите внимание, что если написать просто: ```sql => SELECT '90x60/16'::book_format; ``` ```sql ERROR: malformed record literal: "90x60/16" LINE 1: SELECT '90x60/16'::book_format; ^ DETAIL: Missing left parenthesis. ``` то произойдет ошибка: здесь срабатывает не приведение типа text в book\_format, а создание типа book\_format из литерала, которое использует фиксированный формат для всех составных типов: ```sql => SELECT '(90,60,16)'::book_format; ``` ```text book_format ------------- (90,60,16) (1 row) ``` Теперь заменим тип столбца. Это тяжелая операция, которая перезаписывает всю таблицу, полностью блокируя работу с ней. Поэтому использовать ее надо с осторожностью, особенно для больших таблиц. Вот в каком файле находятся данные сейчас: ```sql => SELECT pg_relation_filepath('books'); ``` ```text pg_relation_filepath ---------------------- base/16388/16415 (1 row) ``` Выполняем замену типа. Преобразование будет выполнено автоматически благодаря созданному ранее приведению типов: ```text => BEGIN; ``` ```text BEGIN ``` ## Page 25: Page 25 {#source-page-025} ```sql => ALTER TABLE books ALTER COLUMN format SET DATA TYPE book_format; ``` ```sql ALTER TABLE ``` ```sql => SELECT relation::regclass, mode FROM pg_locks WHERE relation = 'books'::regclass; ``` ```text relation | mode ----------+--------------------- books | ShareLock books | AccessExclusiveLock (2 rows) ``` Операция выполняется в несколько шагов, но при перезаписи таблицы используется исключительная блокировка. В этой же транзакции надо внести изменения и в интерфейсные функции приложения. Формат возвращают две функции: webapi.get\_catalog и empapi.get\_catalog, но обе они вызывают одну и ту же функцию public.get\_catalog. К счастью, в этой функции столбец format явно приводится к типу text, поэтому никаких изменений не требуется. ```text => COMMIT; ``` ```text COMMIT ``` Теперь данные таблицы находятся в другом файле: ```sql => SELECT pg_relation_filepath('books'); ``` ```text pg_relation_filepath ---------------------- base/16388/16676 (1 row) ``` ## Page 26: Практика+ {#source-page-026} 1. Производственные смены каждого сотрудника фабрики хранятся в таблице. Напишите запрос, который покажет, сколько человеко-часов пропадет даром, если в некоторый момент времени на 15 минут будет отключено электричество. 2. В таблице, содержащей много данных, используется столбец статуса, имеющий тип перечисления. Проверьте, какие блокировки будут установлены на таблицу при необходимости добавления нового статуса, и какое время занимает эта операция. Сравните с альтернативным подходом, при котором перечисление реализуется как обычный текстовый тип с ограничением CHECK на уровне столбца или домена. ### Комментарии преподавателя 1. Производственные смены могут храниться в таблице вида: CREATE TABLE shifts ( employee\_name text, work\_hours tstzrange ); 2. Блокировки можно посмотреть в таблице pg\_locks, как говорилось в теме «Обзор блокировок». Кроме обычной проверки status text CHECK (status IN (...)) рассмотрите также вариант с использованием домена. ## Page 27: 1. Человеко-часы {#source-page-027} ```sql => CREATE DATABASE ext_datatypes; ``` ```sql CREATE DATABASE ``` ```sql => \c ext_datatypes ``` ```text You are now connected to database "ext_datatypes" as user "student". ``` Таблица с производственными сменами: ```sql => CREATE TABLE shifts ( employee_name text, work_hours tstzrange ); ``` ```sql CREATE TABLE ``` Добавим несколько записей (рабочий день с перерывом на обед): ```sql => INSERT INTO shifts VALUES ('alice', '[2020-04-01 09:00,2020-04-01 13:00)'), ('alice', '[2020-04-01 14:00,2020-04-01 18:00)'), ('bob', '[2020-04-01 10:00,2020-04-01 14:00)'), ('bob', '[2020-04-01 15:00,2020-04-01 17:00)'), ('charlie', '[2020-04-01 08:30,2020-04-01 12:30)'), ('charlie', '[2020-04-01 13:30,2020-04-01 17:30)'); ``` ```sql INSERT 0 6 ``` Результат: ```sql => WITH intersection(r) AS ( SELECT work_hours * '[2020-04-01 09:50,2020-04-01 10:05)'::tstzrange FROM shifts ) SELECT sum(upper(r) - lower(r)) FROM intersection; ``` ```text sum ---------- 00:35:00 (1 row) ``` ### 2. Блокировки при добавлении значения Тип перечисления и таблица: ```sql => CREATE TYPE statuses AS ENUM ('todo', 'done'); ``` ```sql CREATE TYPE ``` ```sql => CREATE TABLE process ( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, status statuses ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO process(status) SELECT 'todo' FROM generate_series(1,1_000_000); ``` ```sql INSERT 0 1000000 ``` Начинаем транзакцию: ```text => BEGIN; ``` ```text BEGIN ``` ```text => \timing on ``` ```text Timing is on. ``` ```sql => ALTER TYPE statuses ADD VALUE 'in progress'; ``` ```sql ALTER TYPE Time: 0,292 ms ``` ## Page 28: Page 28 {#source-page-028} ```text => \timing off ``` ```text Timing is off. ``` Выведем только блокировки таблицы: ```sql => SELECT relation::regclass, mode FROM pg_locks WHERE relation = 'process'::regclass; ``` ```text relation | mode ----------+------ (0 rows) ``` ```text => COMMIT; ``` ```text COMMIT ``` Итак: добавление выполняется быстро; таблица не блокируется. Вариант с проверкой CHECK. ```sql => DROP TABLE process; ``` ```sql DROP TABLE ``` ```sql => DROP TYPE statuses; ``` ```sql DROP TYPE ``` ```sql => CREATE TABLE process ( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, status text CHECK (status IN ('todo','done')) ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO process(status) SELECT 'todo' FROM generate_series(1,1_000_000); ``` ```sql INSERT 0 1000000 ``` ```text => BEGIN; ``` ```text BEGIN ``` ```text => \timing on ``` ```text Timing is on. ``` ```sql => ALTER TABLE process DROP CONSTRAINT process_status_check, ADD CHECK (status IN ('todo','done','in progress')); ``` ```sql ALTER TABLE Time: 248,148 ms ``` ```text => \timing off ``` ```text Timing is off. ``` ```sql => SELECT relation::regclass, mode FROM pg_locks WHERE relation = 'process'::regclass; ``` ```text relation | mode ----------+--------------------- process | AccessExclusiveLock (1 row) ``` ```text => COMMIT; ``` ```text COMMIT ``` Здесь: добавление выполняется дольше — поскольку требуется перепроверка всех значений в таблице; таблица полностью блокируется на время изменения ограничения. Это плохой вариант. ## Page 29: Page 29 {#source-page-029} Вариант с проверкой на домене. ```sql => DROP TABLE process; ``` ```sql DROP TABLE ``` ```sql => CREATE DOMAIN statuses AS text CHECK (VALUE IN ('todo','done')); ``` ```sql CREATE DOMAIN ``` ```sql => CREATE TABLE process ( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, status statuses ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO process(status) SELECT 'todo' FROM generate_series(1,1_000_000); ``` ```sql INSERT 0 1000000 ``` ```text => BEGIN; ``` ```text BEGIN ``` ```text => \timing on ``` ```text Timing is on. ``` ```sql => ALTER DOMAIN statuses DROP CONSTRAINT statuses_check; ``` ```sql ALTER DOMAIN Time: 0,580 ms ``` ```sql => ALTER DOMAIN statuses ADD CHECK (VALUE IN ('todo','done','in progress')); ``` ```sql ALTER DOMAIN Time: 214,247 ms ``` ```text => \timing off ``` ```text Timing is off. ``` ```sql => SELECT relation::regclass, mode FROM pg_locks WHERE relation = 'process'::regclass; ``` ```text relation | mode ----------+----------- process | ShareLock (1 row) ``` ```text => COMMIT; ``` ```text COMMIT ``` В этом случае: изменение также выполняется долго; таблица блокируется в более мягком режиме (конфликтует с изменением данных, но разрешает чтение). Это вариант несколько лучше, чем CHECK.