8.16. Составные типы#
8.16. Составные типы
A композитный тип представляет структуру строки или записи; это в основном просто список имен полей и их типов данных. Tantor SE позволяет использовать композитные типы во многих тех же случаях, что и простые типы. Например, столбец таблицы может быть объявлен как композитный тип.
8.16.1. Объявление составных типов
Вот два простых примера определения составных типов:
CREATE TYPE complex AS (
r double precision,
i double precision
);
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);
Синтаксис сравним с CREATE TABLE, за исключением того, что можно указывать только имена полей и их типы; в настоящее время нельзя указывать ограничения (такие как NOT NULL). Обратите внимание, что ключевое слово AS является обязательным; без него система будет считать, что имеется в виду другой тип команды CREATE TYPE, и вы получите странные синтаксические ошибки.
Определив типы, мы можем использовать их для создания таблиц:
CREATE TABLE on_hand (
item inventory_item,
count integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
или функции:
CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric AS 'SELECT $1.price * $2' LANGUAGE SQL; SELECT price_extension(item, 10) FROM on_hand;
Всякий раз, когда вы создаете таблицу, автоматически создается составной тип с тем же именем, что и таблица, чтобы представлять тип строки таблицы. Например, если бы мы сказали:
CREATE TABLE inventory_item (
name text,
supplier_id integer REFERENCES suppliers,
price numeric CHECK (price > 0)
);
тогда тот же самый inventory_item составной тип, показанный выше, возникнет как побочный продукт и может использоваться так же, как и ранее. Однако следует отметить важное ограничение текущей реализации: поскольку составному типу не присвоены ограничения, ограничения, показанные в определении таблицы не применяются к значениям составного типа вне таблицы. (Чтобы обойти это, создайте домен над составным типом и примените желаемые ограничения в виде CHECK ограничений домена).
8.16.2. Создание составных значений
Для записи составного значения в виде литеральной константы, заключите значения полей в скобки и разделите их запятыми. Вы можете поставить двойные кавычки вокруг любого значения поля и должны сделать это, если оно содержит запятые или скобки. (Более подробная информация приведена ниже). Таким образом, общий формат составной константы следующий:
'(val1,val2, ... )'
Пример:
'("fuzzy dice",42,1.99)'
которое было бы допустимым значением типа inventory_item,
определенного выше. Чтобы сделать поле NULL, не пишите никаких символов
на его позиции в списке. Например, эта константа указывает
на NULL третьего поля:
'("fuzzy dice",42,)'
Если вы хотите получить пустую строку вместо NULL, напишите двойные кавычки:
'("",42,)'
Здесь первое поле - непустая строка, не допускающая значений NULL, третье поле - NULL.
(Эти константы на самом деле являются особым случаем общих констант типов, описанных в Раздел 4.1.2.7. Константа изначально рассматривается как строка и передается в процедуру преобразования в составной тип. Возможно, потребуется явное указание типа, чтобы определить, в какой тип преобразовать константу).
Синтаксис выражения ROW также может использоваться для
создания составных значений. В большинстве случаев это гораздо
проще использовать, чем синтаксис строкового литерала, так как вам не нужно
беспокоиться о множественных уровнях кавычек. Мы уже использовали этот
метод выше:
ROW('fuzzy dice', 42, 1.99)
ROW('', 42, NULL)
Ключевое слово ROW на самом деле является необязательным, если у вас есть более одного поля в выражении, поэтому они могут быть упрощены до:
('fuzzy dice', 42, 1.99)
('', 42, NULL)
Синтаксис выражения ROW подробно рассматривается в разделе Раздел 4.2.13.
8.16.3. Доступ к составным типам
Для доступа к полю составного столбца необходимо написать точку и имя поля, подобно выбору поля из имени таблицы. Фактически, это настолько похоже на выбор из имени таблицы, что часто приходится использовать скобки, чтобы не запутать парсер. Например, вы можете попытаться выбрать некоторые подполя из нашей примерной таблицы on_hand с помощью чего-то вроде:
SELECT item.name FROM on_hand WHERE item.price > 9.99;
Это не сработает, так как имя item считается именем таблицы, а не именем столбца on_hand согласно правилам синтаксиса SQL. Вы должны написать это так:
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
или если вам также нужно использовать имя таблицы (например, в многотабличном запросе), вот так:
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
Теперь объект в скобках правильно интерпретируется как ссылка на столбец item, и затем из него можно выбрать подполе.
Аналогичные синтаксические проблемы возникают, когда вы выбираете поле из составного значения. Например, чтобы выбрать только одно поле из результата функции, возвращающей составное значение, вам нужно будет написать что-то вроде:
SELECT (my_func(...)).field FROM ...
Без дополнительных скобок это приведет к синтаксической ошибке.
Специальное поле с именем * означает “все поля”, как
дальше объясняется в Раздел 8.16.5.
8.16.4. Изменение составных типов
Вот несколько примеров правильного синтаксиса для вставки и обновления составных столбцов. Сначала, вставка или обновление всего столбца:
INSERT INTO mytab (complex_col) VALUES((1.1,2.2)); UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;
Первый пример опускает ROW, второй использует его; мы могли бы сделать это любым способом.
Мы можем обновить отдельное подполе составного столбца:
UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;
Обратите внимание, что нам не нужно (и, на самом деле, невозможно) заключать в скобки имя столбца, которое появляется сразу после SET, но нам нужны скобки, когда мы обращаемся к тому же столбцу в выражении справа от знака равенства.
И мы также можем указывать подполя в качестве целей для INSERT:
INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);
Если бы мы не предоставили значения для всех подполей столбца, оставшиеся подполя были бы заполнены значениями null.
8.16.5. Использование составных типов в запросах
Существуют различные специальные правила синтаксиса и поведения, связанные с составными типами в запросах. Эти правила предоставляют полезные ярлыки, но могут быть запутанными, если вы не знаете логики, лежащей в их основе.
В Tantor SE ссылка на имя таблицы (или псевдоним)
в запросе фактически является ссылкой на составное значение
текущей строки таблицы. Например, если у нас есть таблица
inventory_item, как показано
выше, мы можем написать:
SELECT c FROM inventory_item c;
Этот запрос создает один столбец со сложным значением, поэтому мы можем получить вывод вроде:
c
------------------------
("fuzzy dice",42,1.99)
(1 row)
Примечание, однако, что простые имена сопоставляются с именами столбцов перед именами таблиц, поэтому этот пример работает только потому, что в таблицах запроса нет столбца с именем c.
Синтаксис обычного полного имени столбца table_name.column_name можно понимать как применение выбора поля к составному значению текущей строки таблицы. (По причинам эффективности, это на самом деле не реализовано таким образом).
Когда мы пишем
SELECT c.* FROM inventory_item c;
затем, согласно стандарту SQL, мы должны получить содержимое таблицы, развернутое в отдельные столбцы:
name | supplier_id | price
------------+-------------+-------
fuzzy dice | 42 | 1.99
(1 row)
как если бы запрос был
SELECT c.name, c.supplier_id, c.price FROM inventory_item c;
Tantor SE применит это поведение расширения к любому выражению со значением типа composite, хотя, как показано выше, вам нужно заключить в скобки значение, к которому применяется .*, когда это не простое имя таблицы. Например, если myfunc() - это функция, возвращающая составной тип с колонками a, b и c, то эти два запроса имеют одинаковый результат:
SELECT (myfunc(x)).* FROM some_table; SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;
Подсказка
Tantor SE обрабатывает расширение столбцов, превращая первую форму во вторую. Таким образом, в этом примере myfunc() будет вызываться три раза для каждой строки с любым синтаксисом. Если это дорогостоящая функция, вы можете избежать этого с помощью запроса, например:
SELECT m.* FROM some_table, LATERAL myfunc(x) AS m;
Сохранение функции в элементе LATERAL FROM предотвращает ее вызов более одного раза для каждой строки. m.* по-прежнему разворачивается в m.a, m.b, m.c, но теперь эти переменные являются ссылками на вывод элемента FROM. (Ключевое слово LATERAL здесь является необязательным, но мы его показываем, чтобы прояснить, что функция получает x из some_table).
Синтаксис composite_value.* приводит к развертыванию столбцов такого рода, когда он появляется на верхнем уровне списка вывода SELECT, списка RETURNING в командах INSERT/UPDATE/DELETE, в VALUES или в конструкторе строки.
Во всех остальных контекстах (включая вложенные в одну из этих конструкций), присоединение .* к составному значению не изменяет его значение, поскольку оно означает “все столбцы”, и поэтому снова создается то же самое составное значение. Например, если somefunc() принимает составное значение в качестве аргумента, эти запросы эквивалентны:
SELECT somefunc(c.*) FROM inventory_item c; SELECT somefunc(c) FROM inventory_item c;
В обоих случаях текущая строка inventory_item передается функции в качестве одного составного аргумента.
Несмотря на то, что .* ничего не делает в таких случаях, его использование является хорошим стилем, поскольку это ясно указывает на намерение использовать составное значение.
В частности, парсер будет рассматривать c в c.* как имя таблицы или псевдонима, а не как имя столбца, так что нет неоднозначности; тогда как без .* не ясно, означает ли c имя таблицы или имя столбца, и на самом деле предпочтительно будет интерпретация имени столбца, если есть столбец с именем c.
Еще один пример, демонстрирующий эти концепции, заключается в том, что все эти запросы означают одно и то же:
SELECT * FROM inventory_item c ORDER BY c; SELECT * FROM inventory_item c ORDER BY c.*; SELECT * FROM inventory_item c ORDER BY ROW(c.*);
Все эти предложения ORDER BY указывают на составное значение строки, что приводит к сортировке строк в соответствии с правилами, описанными в Раздел 9.24.6. Однако, если в inventory_item был бы столбец с именем c, то первый случай отличался бы от остальных, так как это означало бы сортировку только по этому столбцу. Учитывая ранее показанные имена столбцов, эти запросы также эквивалентны предыдущим:
SELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price); SELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price);
(Последний случай использует конструктор строки с не указанным ключевым словом ROW).
Еще одно особое синтаксическое поведение, связанное с составными значениями, заключается в том, что мы можем использовать функциональную нотацию для извлечения поля из составного значения. Простым способом объяснить это является то, что записи field(table)table.field
SELECT c.name FROM inventory_item c WHERE c.price > 1000; SELECT name(c) FROM inventory_item c WHERE price(c) > 1000;
Кроме того, если у нас есть функция, которая принимает единственный аргумент составного типа, мы можем вызывать ее с использованием любой нотации. Все эти запросы эквивалентны:
SELECT somefunc(c) FROM inventory_item c; SELECT somefunc(c.*) FROM inventory_item c; SELECT c.somefunc FROM inventory_item c;
Это соответствие между функциональной записью и указанием полей позволяет использовать функции на составных типах для реализации
“вычисляемых полей”.
Приложение, использующее последний запрос выше, не должно напрямую знать, что somefunc не является реальным столбцом таблицы.
Подсказка
Из-за такого поведения не рекомендуется давать функции, которая принимает один аргумент типа составного типа, то же самое имя, что и у любого из полей этого составного типа. Если возникает неоднозначность, то будет выбрано поле, если используется синтаксис имени поля, в то время как функция будет выбрана, если используется синтаксис вызова функции. Однако, в версиях PostgreSQL до 11 всегда выбиралась интерпретация поля, если синтаксис вызова требовал этого. Один из способов принудить интерпретацию функции в старых версиях - это указать схему для имени функции, то есть написать schema.функция(значение составного типа)
8.16.6. Синтаксис ввода и вывода составного типа
Внешнее текстовое представление составного значения состоит из элементов, которые интерпретируются в соответствии с правилами преобразования ввода-вывода для отдельных типов полей, а также декорации, которая указывает на составную структуру. Декорация состоит из скобок (( и )) вокруг всего значения, а также запятых (,) между смежными элементами. Пробелы за пределами скобок игнорируются, но внутри скобок они считаются частью значения поля и могут быть значимыми или не значимыми в зависимости от правил преобразования ввода для типа данных поля. Например, в:
'( 42)'
пробелы будут игнорироваться, если тип поля - целое число, но не игнорируются, если это текст.
Как показано ранее, при записи составного значения вы можете заключить в двойные кавычки любое отдельное поле. Вы должны сделать это, если значение поля в противном случае может запутать парсер составного значения. В частности, поля, содержащие скобки, запятые, двойные кавычки или обратные косые черты, должны быть заключены в двойные кавычки. Чтобы вставить двойные кавычки или обратную косую черту в составное поле в кавычках, укажите перед ним обратную косую черту. (Кроме того, пара двойных кавычек внутри поля в кавычках считается символом двойных кавычек, аналогично правилам для апострофов в литеральных строках SQL). Кроме того, вы можете избежать заключения в кавычки и использовать обратную косую черту для экранирования всех символов данных, которые в противном случае будут восприняты как синтаксис составного значения.
Полностью пустое значение поля (нет ни одного символа между запятыми или скобками) представляет собой NULL. Чтобы записать значение, которое является пустой строкой, а не NULL, напишите "".
Композитная выходная процедура будет заключать значения полей в двойные кавычки, если они являются пустыми строками или содержат скобки, запятые, двойные кавычки, обратные косые черты или пробелы. (Это необходимо для улучшения читаемости). Двойные кавычки и обратные косые черты, встроенные в значения полей, будут удвоены.
Примечание
Помните, что то, что вы пишете в SQL-команде, сначала будет интерпретировано
как строковый литерал, а затем как составной. Это удваивает количество
обратных косых черт, которые вам понадобятся (если используется синтаксис экранирования строк).
Например, чтобы вставить поле text,
содержащее двойные кавычки и обратную косую черту в составное
значение, вам нужно будет написать:
INSERT ... VALUES ('("\"\\")');
Процессор строковых литералов удаляет один уровень обратных косых черт, так что то, что поступает на парсер составного значения, выглядит как ("\"\\"). В свою очередь, строка, передаваемая во входную процедуру данных типа text, становится "\. (Если бы мы работали с типом данных, входная процедура которого также обрабатывала бы обратные косые черты особым образом, например, bytea, нам могло бы потребоваться до восьми обратных косых черт в команде, чтобы получить одну обратную косую черту в хранимом составном поле). Для избежания необходимости удваивать обратные косые черты можно использовать долларовую кавычку (см. Раздел 4.1.2.4).
Подсказка
Синтаксис конструктора ROW обычно более удобен для работы
с составными значениями в SQL-командах, чем синтаксис составного литерала.
В ROW отдельные значения полей записываются так же,
как они записываются, когда они не являются членами составного значения.