Составные типы

Составной тип представляет структуру строки или записи; по существу, это просто список имен полей и их типов данных. QHB позволяет использовать составные типы во многом так же, как и простые типы. Например, столбец таблицы можно объявить как составной тип.

Объявление составных типов

Вот два простых примера определения составных типов:

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;

Каждый раз при создании таблицы вместе с ней автоматически создается составной тип с тем же именем, что и таблица. Этот тип представляет тип строки таблицы. Например, при создании таблицы inventory_item:

CREATE TABLE inventory_item (
    name            text,
    supplier_id     integer REFERENCES suppliers,
    price           numeric CHECK (price > 0)
);

в качестве побочного результата возникнет такой же составной тип inventory_item, что и в примере выше, и использовать его можно так же. Однако обратите внимание на важное ограничение текущей реализации: поскольку с составным типом не связаны никакие ограничения, те ограничения, что показаны в определении таблицы, не применяются к значениям составного типа вне таблицы. (Чтобы обойти это, создайте поверх составного типа домен и добавьте желаемые ограничения в виде ограничений CHECK для домена.)

Построение составных значений

Чтобы записать составное значение в виде литеральной константы, заключите значения полей в круглые скобки и разделите их запятыми. Любое значения поля можно заключить в кавычки, и это необходимо делать, если оно содержит запятые или скобки. (Подробнее см. ниже). Таким образом, в общем формате составная константа записывается следующим образом:

'( значение1, значение2, ... )'

Например, запись:

'("fuzzy dice",42,1.99)'

будет допустимым значением типа inventory_item, определенного выше. Чтобы присвоить полю NULL, его позицию в списке нужно оставить пустой. Например, эта константа задает NULL для третьего поля:

'("fuzzy dice",42,)'

Если нужна пустая строка, а не NULL, напишите кавычки:

'("",42,)'

Здесь в первом поле будет пустая строка, а в третьем — NULL.

(Эти константы на самом деле являются лишь частным случаем констант универсального типа, рассматриваемых в подразделе Константы других типов. Константа изначально воспринимается как строка и передается программе преобразования входных данных составного типа. Чтобы определить, к какому типу следует привести константу, может потребоваться явно указать тип.)

Также для создания составных значений можно использовать синтаксис выражения ROW. В большинстве случаев он гораздо удобнее, чем синтаксис строкового литерала, поскольку нет нужды беспокоиться о нескольких уровнях цитирования. Мы уже использовали этот метод ранее:

ROW('fuzzy dice', 42, 1.99)
ROW('', 42, NULL)

На самом деле, если в выражении указано более одного поля, ключевое слово ROW указывать необязательно, поэтому эту запись можно упростить до:

('fuzzy dice', 42, 1.99)
('', 42, NULL)

Синтаксис выражения ROW более подробно рассматривается в подразделе Конструкторы строк.

Обращение к составным типам

Чтобы обратиться к полю составного столбца, нужно написать точку и имя поля, как при выборе поля из имени таблицы. На самом деле, эти обращения почти неотличимы, поэтому зачастую необходимо использовать круглые скобки, чтобы не запутать синтаксический анализатор. Например, можно попробовать выбрать несколько подполей из тестовой таблицы on_hand следующим образом:

SELECT item.name FROM on_hand WHERE item.price > 9.99;

Это не будет работать, так как согласно правилам синтаксиса SQL имя item считается именем таблицы, а не столбца on_hand. Следует написать запрос так:

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 ...

Без дополнительных скобок это приведет к синтаксической ошибке.

Специальное имя поля * означает «все поля»; более подробно это описано в подразделе Использование составных типов в запросах.

Изменение составных типов

Вот несколько примеров правильного написания команд для добавления и изменения составных столбцов. Прежде всего, команды для добавления и изменения всего столбца:

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.

Использование составных типов в запросах

С составными типами в запросах связаны различные специальные синтаксические правила и поведение. Эти правила предоставляют удобные сокращения, но могут вызывать сложности, если не понимать стоящую за ними логику.

В QHB ссылка на имя (или псевдоним) таблицы в запросе фактически является ссылкой на составное значение текущей строки в этой таблице. Например, имея таблицу inventory_item, показанную выше, мы можем написать:

SELECT c FROM inventory_item c;

Этот запрос создает один составной столбец, поэтому результат может быть таким:

           c
------------------------
 ("fuzzy dice",42,1.99)
(1 row)

Однако обратите внимание, что простые имена сопоставляются сначала с именами столбцов и только потом с именами таблиц, поэтому данный пример работает только потому, что в таблицах запроса нет столбца с именем c.

Обычный синтаксис полного имени столбца вида имя_таблицы.имя_столбца можно понимать как применение выбора поля к составному значению текущей строки таблицы. (Из соображений эффективности на самом деле это реализовано по-другому.)

Когда мы пишем

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;

QHB будет применять это развертывание к любому составному выражению, хотя, как показано выше, значение, к которому применяется .*, следует заключать в скобки в случаях, когда это не простое имя таблицы. Например, если myfunc() — это функция, возвращающая составной тип со столбцами a, b и c, то эти два запроса выдадут одинаковый результат:

SELECT (myfunc(x)).* FROM some_table;
SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;

Совет
QHB выполняет развертывание столбцов, фактически превращая первую форму во вторую. Таким образом, в этом примере 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.)

Синтаксис составное_значение.* приводит к такому развертыванию столбца, что он появляется на верхнем уровне выходного списка 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 задают составное значение строки, в результате чего строки сортируются в соответствии с правилами, описанными в подразделе Сравнение составных типов. Однако если строка 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).

Другая особенность синтаксиса, связанная с составными значениями, состоит в том, что для извлечения поля составного значения можно использовать функциональную запись. Это легко объяснить тем, что записи поле(таблица) и таблица.поле взаимозаменяемы. Например, эти запросы равнозначны:

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 — не настоящий столбец таблицы.

Совет
Учитывая это поведение, неразумно давать функции, принимающей один аргумент составного типа, то же имя, что и одному из полей этого составного типа. В случае неоднозначности интерпретация имени поля будет выбрана при использовании синтаксиса обращения к полю, тогда как функция будет выбрана при использовании синтаксиса вызова функции.

Синтаксис вводимых и выводимых значений составного типа

Внешнее текстовое представление составного значения состоит из элементов, которые интерпретируются в соответствии с правилами преобразования ввода/вывода для отдельных типов полей, а также элементов оформления, которые указывают на составную структуру. Оформление состоит из круглых скобок (( и )) вокруг всего значения и запятых (,) между соседними элементами. Пробелы вне скобок игнорируются, но в скобках они считаются частью значения поля и могут учитываться или не учитываться в зависимости от правил преобразования входных данных для типа данных поля. Например, в записи:

'(  42)'

пробел будет игнорироваться, если у поля целочисленный тип, но не текстовый.

Как было показано ранее, при написании составного значения любой его отдельный элемент можно заключить в кавычки. Это нужно делать в том случае, если значение элемента без кавычек может запутать синтаксический анализатор составных значений. Например, в кавычки нужно заключать поля, содержащие круглые скобки, запятые, кавычки или обратные слэши. Чтобы поместить кавычку или обратный слэш в элемент составного значения, заключенного в кавычки, поставьте перед этим символом обратный слэш. (Кроме того, пара кавычек в значении поля, заключенного в кавычки, используется для представления символа одинарной кавычки аналогично правилам для апострофов в строковых литералах SQL.) Или же можно обойтись без кавычек и использовать обратный слэш для экранирования всех символов в данных, которые в противном случае могут быть восприняты как синтаксис составного значения.

Абсолютно пустое значение поля (без запятых или круглых скобок) означает NULL. Чтобы ввести именно пустую строку а не NULL, напишите "".

Программа вывода составных значений помещает кавычки вокруг значений полей, если те являются пустыми строками или содержат круглые скобки, запятые, кавычки, обратный слэш или пробел. (Для пробелов это делать необязательно, но это способствует удобочитаемости.) Кавычки и обратные слэши, заключенные в значения полей, будут удвоены.

Примечание
Помните, что все, что вы пишете в команде SQL, сначала будет интерпретироваться как строковый литерал, а затем как составное значение. Из-за этого количество необходимых обратных слэшей удваивается (при условии использования синтаксиса с экранированием строки). Например, чтобы вставить в составное значение поле text с кавычками и обратным слэшем, нужно написать:

INSERT ... VALUES ('("\"\\")');

Обработчик строковых литералов удаляет один уровень обратного слэша, так что синтаксический анализатор составных значений получает на вход ("\"\\"). В свою очередь, в программу ввода типа данных text попадает уже строка "\. (Если бы мы работали с типом данных, у которого программа ввода также обрабатывает обратный слэш особым образом, например bytea, нам могло бы потребоваться уже восемь обратных слэшей в команде, чтобы получить один в сохраненном поле составного значения.) Во избежание необходимости такого дублирования символов строки можно заключать в знаки доллара (см. раздел Строковые константы с экранированием знаками доллара).

Совет
При записи составных значений в командах SQL зачастую удобнее работать с синтаксисом конструктора ROW, чем с синтаксисом литерала составного типа. В ROW значения отдельных элементов записываются так же, как если бы они не были членами составного выражения.

Copyright © KVANTOM LLC, 2019-2024