# Занятие 10: SQL / Составные типы > Исходная страница в нативном Markdown: SQL / Составные типы --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [dev1_10_sql_row.html](https://edu.postgrespro.ru/16/dev1-16/dev1_10_sql_row.html) > **Archive:** [DEV1-handouts-16.zip](https://edu.postgrespro.ru/16/DEV1-handouts-16.zip) > **Страниц PDF:** 24 ## Page 1: SQL / Составные типы {#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} **Составной тип** - набор именованных атрибутов (полей) - то же, что табличная строка, но без ограничений целостности - Создание - явное объявление нового типа - неявно при создании таблицы - неопределенный составной тип record - Использование - атрибуты как скалярные значения - операции со значениями составного типа: сравнение, проверка - на NULL, использование с подзапросами ### Комментарии преподавателя Составной тип — это набор атрибутов, каждый из которых имеет свое имя и свой тип. Составной тип можно рассматривать как табличную строку. Часто он называется «записью» (а в Си-подобных языках такой тип называется «структурой»). Составной тип — объект базы данных, его объявление регистрирует новый тип в системном каталоге, после чего он становится полноценным типом SQL. При создании таблицы автоматически создается и одноименный составной тип, представляющий строку этой таблицы. Важное отличие состоит в том, что в составном типе нет ограничений целостности. Атрибуты составного типа могут использоваться как отдельные значения (хотя атрибут, в свою очередь, тоже может иметь составной тип). Составной тип можно использовать как любой другой тип SQL, например, создавать столбцы таблиц этого типа и т. п. Значения составного типа можно сравнивать между собой, проверять на неопределенность (NULL), использовать с подзапросами в таких конструкциях, как IN, ANY/SOME, ALL. ## Page 4: Явное объявление составного типа {#source-page-004} Первый способ ввести составной тип — явным образом объявить его: ```sql => CREATE TYPE currency AS ( amount numeric, code text ); ``` ```sql CREATE TYPE ``` ```sql => \dT ``` ```text List of data types Schema | Name | Description --------+----------+------------- public | currency | (1 row) ``` Такой тип можно использовать точно так же, как любой другой тип SQL. Например, мы можем создать таблицу со столбцами такого типа: ```sql => CREATE TABLE transactions( account_id integer, debit currency, credit currency, date_entered date DEFAULT current_date ); ``` ```sql CREATE TABLE ``` Нужно ли так делать — вопрос сложный, универсальных рецептов тут не существует. В каких-то случаях это может быть полезно; в каких-то удобнее действовать в реляционных рамках: выделить сущность, представляемую типом, в отдельную таблицу и ссылаться на нее. Это позволить избежать избыточности данных (нормализация) и упростить индексирование (в случае составного типа скорее всего потребуется индекс по выражению). В целом PostgreSQL обладает достаточно большим количеством встроенных типов данных, так что, вероятно, необходимость в создании собственного типа будет возникать не часто, если не сказать редко. ### Конструирование значений составных типов Значения составного типа можно формировать в виде строки, внутри которой в скобках перечислены значения. Обратите внимание, что строковые значения заключается в двойные кавычки: ```sql => INSERT INTO transactions VALUES (1, NULL, '(100.00,"RUB")'); ``` ```sql INSERT 0 1 ``` Другой способ — конструктор ROW: ```sql => INSERT INTO transactions VALUES (2, ROW(80.00,'RUB'), NULL); ``` ```sql INSERT 0 1 ``` Если составной тип содержит более одного поля, то слово ROW можно опустить: ```sql => INSERT INTO transactions VALUES (3, (20.00,'RUB'), NULL); ``` ```sql INSERT 0 1 ``` ```sql => SELECT * FROM transactions; ``` ```text account_id | debit | credit | date_entered ------------+-------------+--------------+-------------- 1 | | (100.00,RUB) | 2026-04-10 2 | (80.00,RUB) | | 2026-04-10 3 | (20.00,RUB) | | 2026-04-10 (3 rows) ``` ### Атрибуты составного типа как отдельные значения Обращение к отдельному атрибуту составного типа — по сути то же, что и обращению к столбцу таблицы, ведь строка таблицы — это и есть составной тип: ## Page 5: Page 5 {#source-page-005} ```sql => SELECT t.account_id FROM transactions t; ``` ```text account_id ------------ 1 2 3 (3 rows) ``` Как правило, требуется брать составное значение в скобки, например, чтобы отличать атрибут записи от столбца таблицы: ```sql => SELECT (t.debit).amount, (t.credit).amount FROM transactions t; ``` ```text amount | amount --------+-------- | 100.00 80.00 | 20.00 | (3 rows) ``` Или в случае, когда используется выражение: ```sql => SELECT ((10.00,'RUB')::currency).amount; ``` ```text amount -------- 10.00 (1 row) ``` Составное значение не обязательно связано с каким-то конкретным типом, оно может быть неопределенной записью псевдотипа record: ```sql => SELECT (10.00,'RUB')::record; ``` ```text row ------------- (10.00,RUB) (1 row) ``` Но получится ли обратиться к атрибуту такой записи? ```sql => SELECT ((10.00,'RUB')::record).amount; ``` ```sql ERROR: could not identify column "amount" in record data type LINE 1: SELECT ((10.00,'RUB')::record).amount; ^ ``` Нет, поскольку атрибуты такого типа безымянные. ### Неявный составной тип для таблиц Более частое на практике применение составных типов — упрощение работы функций с таблицами. При создании таблицы неявно создается и одноименный составной тип. Например, места в кинотеатре: ```sql => CREATE TABLE seats( line text, number integer ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO seats VALUES ('A', 42), ('B', 1), ('C', 27); ``` ```sql INSERT 0 3 ``` Команда \\dT «прячет» такие неявные типы, но при желании их можно увидеть непосредственно в таблице pg\_type: ```sql => SELECT typtype FROM pg_type WHERE typname = 'seats'; ``` ```text typtype --------- c (1 row) ``` c — composite, то есть составной тип. ## Page 6: Операции над значениями составных типов {#source-page-006} Значения составных типов можно сравнивать между собой. Это происходит поэлементно (примерно так же, как строки сравниваются посимвольно): ```sql => SELECT * FROM seats s WHERE s < ('B',52)::seats; ``` ```text line | number ------+-------- A | 42 B | 1 (2 rows) ``` Осторожно: существует много тонкостей, связанных с неопределенными значениями внутри записей. Также работает проверка на неопределенность IS [NOT] NULL и сравнение IS [NOT] DISTINCT FROM. Составные типы можно использовать с подзапросами, что бывает очень удобно. Добавим таблицу с билетами: ```sql => CREATE TABLE tickets( line text, number integer, movie_start date ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO tickets VALUES ('A', 42, current_date), ('B', 1, current_date+1); ``` ```sql INSERT 0 2 ``` Теперь, например, можно написать такой запрос для поиска мест в билетах на сегодняшний сеанс: ```sql => SELECT * FROM seats WHERE (line, number) IN ( SELECT line, number FROM tickets WHERE movie_start = current_date ); ``` ```text line | number ------+-------- A | 42 (1 row) ``` Без возможности использовать подзапрос пришлось бы явно соединять таблицы. ## Page 7: Параметры подпрограмм {#source-page-007} **Подпрограмма может принимать параметры составного типа** - Способ реализации вычисляемых полей - взаимозаменяемость table.column и column(table) - Другие способы - представления - столбцы GENERATED ALWAYS ### Комментарии преподавателя Разумеется, подпрограммы могут принимать параметры составных типов. Интересно, что для доступа к столбцу таблицы можно использовать не только привычную форму «таблица.столбец», но и функциональную: «столбец(таблица)». Это позволяет создавать вычисляемые поля, определяя функцию, принимающую на вход составной тип. Это несколько курьезный способ, поскольку того же результата можно добиться более явно с помощью представления. Стандарт SQL также предусматривает генерируемые (GENERATED ALWAYS) столбцы, хотя в PostgreSQL эта возможность пока реализована не в соответствии со стандартом — столбцы не вычисляются на лету, а сохраняются в таблице. ## Page 8: Параметры составного типа {#source-page-008} Определим функцию, принимающую значение составного типа и возвращающую текстовый номер места. ```sql => CREATE FUNCTION seat_no(seat seats) RETURNS text IMMUTABLE LANGUAGE sql RETURN seat.line || seat.number; ``` ```sql CREATE FUNCTION ``` Обратите внимание, что в общем случае конкатенация имеет категорию изменчивости stable, а не immutable, поскольку для некоторых типов данных приведение к строке может давать разные результаты в зависимости от настроек. ```sql => SELECT seat_no(ROW('A',42)); ``` ```text seat_no --------- A42 (1 row) ``` Что удобно, такой функции можно передавать непосредственно строку таблицы: ```sql => SELECT s.line, s.number, seat_no(s.*) FROM seats s; ``` ```text line | number | seat_no ------+--------+--------- A | 42 | A42 B | 1 | B1 C | 27 | C27 (3 rows) ``` Можно обойтись и без «звездочки»: ```sql => SELECT s.line, s.number, seat_no(s) FROM seats s; ``` ```text line | number | seat_no ------+--------+--------- A | 42 | A42 B | 1 | B1 C | 27 | C27 (3 rows) ``` Синтаксисом допускается обращение к функции как к столбцу таблицы (и наоборот, к столбцу как к функции): ```sql => SELECT s.line, number(s), s.seat_no FROM seats s; ``` ```text line | number | seat_no ------+--------+--------- A | 42 | A42 B | 1 | B1 C | 27 | C27 (3 rows) ``` Таким образом можно использовать функции как вычисляемые «на лету» столбцы таблиц. Что, если и в таблице окажется столбец с тем же именем? Раньше в любом случае предпочтение отдавалось столбцу, а начиная с версии 11 выбор зависит от синтаксической формы. Разумеется, такого же эффекта можно добиться, определив представление. ```sql => CREATE VIEW seats_v AS SELECT s.line, s.number, seat_no(s) FROM seats s; ``` ```sql CREATE VIEW ``` ```sql => SELECT line, number, seat_no FROM seats_v; ``` ```text line | number | seat_no ------+--------+--------- A | 42 | A42 B | 1 | B1 C | 27 | C27 (3 rows) ``` ## Page 9: Page 9 {#source-page-009} А начиная с версии 12, PostgreSQL позволяет при создании таблиц объявить «настоящие» вычисляемые столбцы. Правда, в отличие от стандарта SQL, такие столбцы не вычисляются на лету, а сохраняются в таблице: ```sql => CREATE TABLE seats2( line text, number integer, seat_no text GENERATED ALWAYS AS (seat_no(ROW(line,number))) STORED ); ``` ```sql CREATE TABLE ``` ```sql => \d seats2 ``` ```text Table "public.seats2" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+----------------------------------------------- ---------- line | text | | | number | integer | | | seat_no | text | | | generated always as (seat_no(ROW(line, number))) stored ``` ```sql => INSERT INTO seats2 (line, number) SELECT line, number FROM seats; ``` ```sql INSERT 0 3 ``` ```sql => SELECT * FROM seats2; ``` ```text line | number | seat_no ------+--------+--------- A | 42 | A42 B | 1 | B1 C | 27 | C27 (3 rows) ``` Если мы впоследствии решили, что следует явно задавать значение поля, то нам достаточно удалить выражение: ```sql => ALTER TABLE seats2 ALTER COLUMN seat_no DROP EXPRESSION; ``` ```sql ALTER TABLE ``` Существующие данные в столбце остались, но столбец теперь не вычисляемый, а «обычный»: ```sql => SELECT * FROM seats2; ``` ```text line | number | seat_no ------+--------+--------- A | 42 | A42 B | 1 | B1 C | 27 | C27 (3 rows) ``` ```sql => \d seats2 ``` ```text Table "public.seats2" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- line | text | | | number | integer | | | seat_no | text | | | ``` ## Page 10: «Однострочные» функции {#source-page-010} **Возвращают значение составного типа** - Обычно вызываются в списке выборки запроса - При вызове в предложении FROM возвращают однострочную таблицу ### Комментарии преподавателя Функции могут не только принимать параметры составного типа, но и возвращать значение составного типа. Обычно функции вызываются в списке выборки запроса (предложение SELECT). Но функцию можно вызвать и в предложении FROM, как будто таблицу из одной строки. ## Page 11: Функции, возвращающие одно значение {#source-page-011} Напишем функцию, конструирующую и возвращающую табличную строку по отдельным компонентам. Такую функцию можно объявить как RETURNS seats: ```sql => CREATE FUNCTION seat(line text, number integer) RETURNS seats IMMUTABLE LANGUAGE sql RETURN ROW(line, number)::seats; ``` ```sql CREATE FUNCTION ``` ```sql => SELECT seat('A', 42); ``` ```text seat -------- (A,42) (1 row) ``` Мы получаем результат составного типа. Его можно «развернуть» в однострочную таблицу: ```sql => SELECT (seat('A', 42)).*; ``` ```text line | number ------+-------- A | 42 (1 row) ``` Имена столбцов и их типы получены здесь из описания составного типа seats. Но функцию можно вызывать не только в списке выборки запроса или в условиях, как часть выражения. К функции можно обратиться и в предложении FROM, как к таблице: ```sql => SELECT * FROM seat('A', 42); ``` ```text line | number ------+-------- A | 42 (1 row) ``` При этом мы тоже получаем однострочную таблицу. Кстати, можно ли подобным образом вызвать функцию, возвращающую отдельное (скалярное) значение? ```sql => SELECT * FROM abs(-1.5); ``` ```text abs ----- 1.5 (1 row) ``` Да, так тоже можно: мы получили одну строку, состоящую из одного столбца. Другой вариант, который мы уже видели в теме «SQL. Функции» — объявить выходные параметры. Заодно отметим, что в запросе не обязательно собирать составной тип из отдельных полей — это будет проделано автоматически: ```sql => DROP FUNCTION seat(text, integer); ``` ```sql DROP FUNCTION ``` ```sql => CREATE FUNCTION seat(line INOUT text, number INOUT integer) IMMUTABLE LANGUAGE sql BEGIN ATOMIC SELECT line, number; END; ``` ```sql CREATE FUNCTION ``` ```sql => SELECT seat('A', 42); ``` ```text seat -------- (A,42) (1 row) ``` ## Page 12: Page 12 {#source-page-012} ```sql => SELECT * FROM seat('A', 42); ``` ```text line | number ------+-------- A | 42 (1 row) ``` Получаем тот же результат — но имена и типы полей в данном случае получены из выходных параметров функции, а сам составной тип остается анонимным. И еще один вариант — объявить функцию как возвращающую псевдотип record, который обозначает составной тип «вообще», без уточнения его структуры. ```sql => DROP FUNCTION seat(text, integer); ``` ```sql DROP FUNCTION ``` ```sql => CREATE FUNCTION seat(line text, number integer) RETURNS record IMMUTABLE LANGUAGE sql RETURN (line, number); ``` ```sql CREATE FUNCTION ``` ```sql => SELECT seat('A',42); ``` ```text seat -------- (A,42) (1 row) ``` Но вызвать такую функцию в предложении FROM уже не получится, поскольку возвращаемый составной тип не просто анонимный, но и количество и типы его полей заранее (на этапе разбора запроса) неизвестны: ```sql => SELECT * FROM seat('A',42); ``` ```sql ERROR: a column definition list is required for functions returning "record" LINE 1: SELECT * FROM seat('A',42); ^ ``` В этом случае при вызове функции структуру составного типа придется уточнить — мы явно указываем названия и типы столбцов: ```sql => SELECT * FROM seat('A',42) AS (line text, number integer); ``` ```text line | number ------+-------- A | 42 (1 row) ``` При написании функций допустим любой из этих трех вариантов, но лучше сразу подумать об использовании: будет ли удобен анонимный тип и уточнение структуры типа при вызове. ## Page 13: Табличные функции {#source-page-013} **Объявляются как RETURNS SETOF или RETURNS TABLE** - Могут возвращать несколько строк - Обычно вызываются в предложении FROM - Можно использовать как представление с параметрами - особенно удобно в сочетании с подстановкой тела функции в запрос ### Комментарии преподавателя Мы знаем, что к функциям можно обращаться в предложении FROM, однако до сих пор результатом была одна строка. Конечно, интереснее было бы иметь функции, возвращающие множество строк (табличные функции) — и их действительно можно определять. Табличные функции естественно вызывать в предложении FROM и можно рассматривать их как своеобразное представление. (Формально PostgreSQL позволяет вызвать такую функцию и в списке выборки, но так лучше не делать.) Как и с обычными функциями, в ряде случаев планировщик может подставить тело функции в основной запрос. Это позволяет создавать «представления с параметрами» без накладных расходов. ## Page 14: Функции, возвращающие множество строк (табличные функции) {#source-page-014} Напишем функцию, которая вернет все места в прямоугольном зале заданного размера. ```sql => CREATE FUNCTION rect_hall(max_line integer, max_number integer) RETURNS SETOF seats IMMUTABLE LANGUAGE sql BEGIN ATOMIC SELECT chr(line+64), number FROM generate_series(1,max_line) AS line, generate_series(1,max_number) AS number; END; ``` ```sql CREATE FUNCTION ``` Ключевое отличие — слово SETOF. В таком случае функция возвращает не первую строку последнего запроса, как обычно, а все строки последнего запроса. ```sql => SELECT * FROM rect_hall(max_line => 2, max_number => 3); ``` ```text line | number ------+-------- A | 1 A | 2 A | 3 B | 1 B | 2 B | 3 (6 rows) ``` Вместо SETOF seats можно использовать и SETOF record: ```sql => DROP FUNCTION rect_hall(integer, integer); ``` ```sql DROP FUNCTION ``` ```sql => CREATE FUNCTION rect_hall(max_line integer, max_number integer) RETURNS SETOF record IMMUTABLE LANGUAGE sql BEGIN ATOMIC SELECT chr(line+64), number FROM generate_series(1,max_line) AS line, generate_series(1,max_number) AS number; END; ``` ```sql CREATE FUNCTION ``` Но в этом случае, как мы видели, при вызове функции придется уточнять структуру составного типа: ```sql => SELECT * FROM rect_hall(max_line => 2, max_number => 3) AS (a_line text, a_number integer); ``` ```text a_line | a_number --------+---------- A | 1 A | 2 A | 3 B | 1 B | 2 B | 3 (6 rows) ``` А можно объявить функцию с выходными параметрами. Но SETOF record все равно придется написать, чтобы показать, что функция возвращает не одну строку, а множество: ```sql => DROP FUNCTION rect_hall(integer, integer); ``` ```sql DROP FUNCTION ``` ## Page 15: Page 15 {#source-page-015} ```sql => CREATE FUNCTION rect_hall( max_line integer, max_number integer, OUT p_line text, OUT p_number integer ) RETURNS SETOF record IMMUTABLE LANGUAGE sql BEGIN ATOMIC SELECT chr(line+64), number FROM generate_series(1,max_line) AS line, generate_series(1,max_number) AS number; END; ``` ```sql CREATE FUNCTION ``` ```sql => SELECT * FROM rect_hall(max_line => 2, max_number => 3); ``` ```text p_line | p_number --------+---------- A | 1 A | 2 A | 3 B | 1 B | 2 B | 3 (6 rows) ``` Еще один равнозначный (и к тому же описанный в стандарте SQL) способ объявить табличную функцию — указать слово TABLE: ```sql => DROP FUNCTION rect_hall(integer, integer); ``` ```sql DROP FUNCTION ``` ```sql => CREATE FUNCTION rect_hall(max_line integer, max_number integer) RETURNS TABLE(t_line text, t_number integer) LANGUAGE sql BEGIN ATOMIC SELECT chr(line+64), number FROM generate_series(1,max_line) AS line, generate_series(1,max_number) AS number; END; ``` ```sql CREATE FUNCTION ``` ```sql => SELECT * FROM rect_hall(max_line => 2, max_number => 3); ``` ```text t_line | t_number --------+---------- A | 1 A | 2 A | 3 B | 1 B | 2 B | 3 (6 rows) ``` Иногда в запросах бывает полезно пронумеровать строки в том порядке, в котором они получены от функции. Для этого есть специальная конструкция: ```sql => SELECT * FROM rect_hall(max_line => 2, max_number => 3) WITH ORDINALITY; ``` ```text t_line | t_number | ordinality --------+----------+------------ A | 1 | 1 A | 2 | 2 A | 3 | 3 B | 1 | 4 B | 2 | 5 B | 3 | 6 (6 rows) ``` При использовании функции в предложении FROM, перед ней неявно подразумевается ключевое слово LATERAL, что позволяет функции обращаться к столбцам таблиц, стоящих в запросе слева от нее. Иногда это позволяет упростить формулировку запросов. Например, напишем функцию, конструирующую зал наподобие амфитеатра, в котором дальние ряды имеют больше мест, чем ближние: ## Page 16: Page 16 {#source-page-016} ```sql => CREATE FUNCTION amphitheatre(max_line integer) RETURNS TABLE(t_line text, t_number integer) IMMUTABLE LANGUAGE sql BEGIN ATOMIC SELECT chr(line + 64), number FROM generate_series(1,max_line) AS line, -- <--+ generate_series(1, -- | line ----------------------+ ) AS number; END; ``` ```sql CREATE FUNCTION ``` ```sql => SELECT * FROM amphitheatre(3); ``` ```text t_line | t_number --------+---------- A | 1 B | 1 B | 2 C | 1 C | 2 C | 3 (6 rows) ``` Интересно, что к функции, возвращающей множество строк, можно обратиться и в списке выборки запроса: ```sql => SELECT rect_hall(3,4); ``` ```text rect_hall ----------- (A,1) (A,2) (A,3) (A,4) (B,1) (B,2) (B,3) (B,4) (C,1) (C,2) (C,3) (C,4) (12 rows) ``` В некоторых случаях это смотрится логично, но иногда результат может удивить. Например, сколько строк вернет такой запрос? ```sql => SELECT rect_hall(2,3), rect_hall(2,2); ``` ```text rect_hall | rect_hall -----------+----------- (A,1) | (A,1) (A,2) | (A,2) (A,3) | (B,1) (B,1) | (B,2) (B,2) | (B,3) | (6 rows) ``` Получается 6 строк, а в версиях до 10 получалось наименьшее общее кратное числа строк, возвращенных каждой функцией (12 в данном случае). Еще хуже, что запрос может вернуть меньше строк, чем ожидалось, если функция не вернет ни одной строки при каком-то значении параметров. Поэтому такой способ вызова не стоит использовать. ### Функции как представления с параметрами Как мы видели, функцию можно использовать во фразе FROM как таблицу или представление. Но при этом мы дополнительно получаем возможность использовать параметры, что в ряде случаев бывает удобно. Единственная сложность с таким подходом состоит в том, что при обращении к функции (Function Scan) запросы из нее сначала выполняются полностью, и только затем к результату применяются дополнительные условия из запроса. ## Page 17: Page 17 {#source-page-017} ```sql => EXPLAIN (costs off) ``` ```sql SELECT * FROM rect_hall(3,4) WHERE t_line = 'A'; ``` ```text QUERY PLAN -------------------------------- Function Scan on rect_hall Filter: (t_line = 'A'::text) (2 rows) ``` Если бы функция содержала сложный, долгий запрос, это могло бы стать проблемой. В некоторых случаях тело функции может подставляться в вызывающий запрос. Для табличных функций ограничения более мягкие: функция написана на языке SQL; функция сама не должна быть изменчивой (VOLATILE) и не должна содержать вызовов таких функций; функция не должна быть строгой (STRICT); тело должно содержать единственный оператор SELECT (но он может быть сложным); и ряд других ограничений. В нашем случае дело в том, что последний раз мы объявили функцию как изменчивую, не указав категорию изменчивости явно. ```sql => ALTER FUNCTION rect_hall(integer, integer) IMMUTABLE; ``` ```sql ALTER FUNCTION ``` ```sql => EXPLAIN (costs off) SELECT * FROM rect_hall(3,4) WHERE t_line = 'A'; ``` ```text QUERY PLAN ------------------------------------------------ Nested Loop -> Function Scan on generate_series line Filter: (chr((line + 64)) = 'A'::text) -> Function Scan on generate_series number (4 rows) ``` Теперь нет вызова функции как такового, а условие подставлено внутрь запроса, что более эффективно. ## Page 18: Итоги {#source-page-018} **Составной тип объединяет значения других типов** - Упрощает и обогащает работу функций с таблицами - Позволяет создавать вычислимые поля и представления с параметрами - Функции могут возвращать множество строк ## Page 19: Практика {#source-page-019} 1. Создайте функцию onhand\_qty для подсчета имеющихся в наличии книг. Функция принимает параметр составного типа books и возвращает целое число. - Используйте эту функцию в представлении catalog\_v - в качестве «вычисляемого поля». - Проверьте, что приложение отображает количество книг. 2. Создайте табличную функцию get\_catalog для поиска книг. Функция принимает значения полей формы поиска («имя автора», «название книги», «есть на складе») и возвращает подходящие книги в формате catalog\_v. - Проверьте, что в «Магазине» начал работать поиск и - просмотр. ### Комментарии преподавателя 1. ```text FUNCTION onhand_qty(book books) RETURNS integer ``` 2. ```text 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, передает ли он в этом случае пустые строки или неопределенные значения? ## Page 20: 1. Функция onhand_qty {#source-page-020} ```sql => CREATE FUNCTION onhand_qty(book books) RETURNS integer STABLE LANGUAGE sql BEGIN ATOMIC SELECT coalesce(sum(o.qty_change),0)::integer FROM operations o WHERE o.book_id = book.book_id; END; ``` ```sql CREATE FUNCTION ``` ```sql => CREATE OR REPLACE VIEW catalog_v AS SELECT b.book_id, book_name(b.book_id, b.title) AS display_name, b.onhand_qty FROM books b ORDER BY display_name; ``` ```sql CREATE VIEW ``` ### 2. Функция get\\\_catalog Расширяем catalog\_v заголовком книги и полным списком авторов (приложение игнорирует неизвестные ему поля). Функция, возвращающая полный список авторов: ```sql => CREATE FUNCTION authors(book books) RETURNS text STABLE LANGUAGE sql BEGIN ATOMIC SELECT string_agg( a.last_name || ' ' || a.first_name || coalesce(' ' || nullif(a.middle_name,''), ''), ', ' ORDER BY ash.seq_num ) FROM authors a JOIN authorship ash ON a.author_id = ash.author_id WHERE ash.book_id = book.book_id; END; ``` ```sql CREATE FUNCTION ``` Используем эту функцию в представлении catalog\_v. Такое представление уже существует; мы пересоздадим его — изменим порядок столбцов и запрос: ```sql => DROP VIEW catalog_v; ``` ```sql DROP VIEW ``` ```sql => CREATE VIEW catalog_v AS SELECT b.book_id, b.title, b.onhand_qty, book_name(b.book_id, b.title) AS display_name, b.authors FROM books b ORDER BY display_name; ``` ```sql CREATE VIEW ``` Функция get\_catalog теперь использует расширенное представление: ## Page 21: Page 21 {#source-page-021} ```sql => CREATE FUNCTION get_catalog( author_name text, book_title text, in_stock boolean ) RETURNS TABLE(book_id integer, display_name text, onhand_qty integer) STABLE LANGUAGE sql BEGIN ATOMIC SELECT cv.book_id, cv.display_name, cv.onhand_qty FROM catalog_v cv ``` ```sql WHERE cv.title ILIKE '%'||coalesce(book_title,'')||'%' AND cv.authors ILIKE '%'||coalesce(author_name,'')||'%' AND (in_stock AND cv.onhand_qty > 0 OR in_stock IS NOT TRUE) ORDER BY display_name; END; ``` ```sql CREATE FUNCTION ``` ## Page 22: Практика+ {#source-page-022} 1. Напишите функцию, переводящую строку, содержащую число в шестнадцатеричной системе, в обычное целое число. Например: convert('FF') → 255 2. Добавьте в функцию второй необязательный параметр — основание системы счисления (по умолчанию — 16). Например: convert('0110',2) → 6 3. Табличная функция generate\_series не работает со строковыми типами. Предложите свою функцию для генерации последовательностей строк из заглавных английских букв. ### Комментарии преподавателя 1. Например: ```text convert('FF') → 255 ``` Для решения пригодятся: табличная функция regexp\_split\_to\_table, функции upper и reverse, конструкция WITH ORDINALITY. Другое решение возможно с помощью рекурсивного запроса. Проверить реализацию можно, используя шестнадцатеричные константы: SELECT X'FF'::integer; 2. Например: ```text convert('0110',2) → 6 ``` 3. Считайте, что на вход подаются строки равной длины. Например: ```text generate_series('AA','ZZ') → → 'AA' 'AB' 'AC' ... 'ZY' 'ZZ' ``` ## Page 23: 1. Функция для шестнадцатеричной системы {#source-page-023} ```sql => CREATE DATABASE sql_row; ``` ```sql CREATE DATABASE ``` ```sql => \c sql_row ``` ```text You are now connected to database "sql_row" as user "student". ``` Сначала для удобства определим функцию для одной цифры: ```sql => CREATE FUNCTION digit(d text) RETURNS integer IMMUTABLE LANGUAGE sql RETURN ascii(d) - CASE WHEN d BETWEEN '0' AND '9' THEN ascii('0') ELSE ascii('A') - 10 END; ``` ```sql CREATE FUNCTION ``` Теперь основная функция: ```sql => CREATE FUNCTION convert(hex text) RETURNS integer IMMUTABLE LANGUAGE sql BEGIN ATOMIC WITH s(d,ord) AS ( SELECT * FROM regexp_split_to_table(reverse(upper(hex)),'') WITH ORDINALITY ) SELECT sum(digit(d) * 16^(ord-1))::integer FROM s; END; ``` ```sql CREATE FUNCTION ``` ```sql => SELECT convert('0FE'), convert('0FF'), convert('100'); ``` ```text convert | convert | convert ---------+---------+--------- 254 | 255 | 256 (1 row) ``` ### 2. Функция для любой системы счисления Предполагаем, что основание системы счисления от 2 до 36, то есть число записывается цифрами от 0 до 9, либо буквами от A до Z. В этом случае изменения минимальные. ```sql => DROP FUNCTION convert(text); ``` ```sql DROP FUNCTION ``` ```sql => CREATE FUNCTION convert(num text, radix integer DEFAULT 16) RETURNS integer IMMUTABLE LANGUAGE sql BEGIN ATOMIC WITH s(d,ord) AS ( SELECT * FROM regexp_split_to_table(reverse(upper(num)),'') WITH ORDINALITY ) SELECT sum(digit(d) * radix^(ord-1))::integer FROM s; END; ``` ```sql CREATE FUNCTION ``` ```sql => SELECT convert('101100', 2), convert('2C'), convert('54', 8); ``` ```text convert | convert | convert ---------+---------+--------- 44 | 44 | 44 (1 row) ``` Заметим, что в PostgreSQL начиная с версии 16 есть возможность записи целочисленных констант не только в десятичном, но и в двоичном, шестнадцатеричном и восьмеричном виде. Такая возможность закреплена в современном стандарте SQL: ```sql => SELECT 0b101100 AS bin, 0x2C AS hex, 0o54 AS oct; ``` ## Page 24: 3. Функция generate_series для строк {#source-page-024} ```text bin | hex | oct -----+-----+----- 44 | 44 | 44 (1 row) ``` Сначала напишем вспомогательные функции, переводящие строку в числовое представление и обратно. Первая очень похожа на функцию из предыдущего задания: ```sql => CREATE FUNCTION text2num(s text) RETURNS integer IMMUTABLE LANGUAGE sql BEGIN ATOMIC WITH s(d,ord) AS ( SELECT * FROM regexp_split_to_table(reverse(s),'') WITH ORDINALITY ) SELECT sum( (ascii(d)-ascii('A')) * 26^(ord-1))::integer FROM s; END; ``` ```sql CREATE FUNCTION ``` Обратную функцию напишем с помощью рекурсивного запроса: ```sql => CREATE FUNCTION num2text(n integer, digits integer) RETURNS text IMMUTABLE LANGUAGE sql BEGIN ATOMIC WITH RECURSIVE r(num,txt, level) AS ( SELECT n/26, chr( n%26 + ascii('A') )::text, 1 UNION ALL SELECT r.num/26, chr( r.num%26 + ascii('A') ) || r.txt, r.level+1 FROM r WHERE r.level < digits ) SELECT r.txt FROM r WHERE r.level = digits; END; ``` ```sql CREATE FUNCTION ``` ```sql => SELECT num2text( text2num('ABC'), length('ABC') ); ``` ```text num2text ---------- ABC (1 row) ``` Теперь функцию generate\_series для строк можно переписать, используя generate\_series для целых чисел. ```sql => CREATE FUNCTION generate_series(start text, stop text) RETURNS SETOF text IMMUTABLE LANGUAGE sql BEGIN ATOMIC SELECT num2text( g.n, length(start)) FROM generate_series(text2num(start), text2num(stop)) g(n); END; ``` ```sql CREATE FUNCTION ``` ```sql => SELECT generate_series('AZ','BC'); ``` ```text generate_series ----------------- AZ BA BB BC (4 rows) ``` ```sql => \c postgres ``` ```text You are now connected to database "postgres" as user "student". ``` ```sql => DROP DATABASE sql_row; ``` ```sql DROP DATABASE ```