| Документация по PostgreSQL 9.4.1 | |||
|---|---|---|---|
| Пред. | Уровень выше | Глава 8. Типы данных | След. |
8.16. Составные типы
Составной тип представляет структуру табличной строки или записи; по сути это просто список имён полей и соответствующих типов данных. PostgreSQL позволяет использовать составные типы во многом так же, как и простые типы. Например, в определении таблицы можно объявить колонку составного типа.
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, в точности соответствующий тому, что был показан выше, и использовать его можно так же. Заметьте, что в текущей реализации есть один недостаток: так как с составным типом не могут быть связаны ограничения, описанные в определении таблицы ограничения не применяются к значениям составного типа вне таблицы. (В некоторой степени это можно обойти, используя в составных типах домены.)
8.16.2. Ввод значения составного типа
Чтобы записать значение составного типа в виде текстовой константы, его поля нужно заключить в круглые скобки и разделить их запятыми. Значение любого поля можно заключить в кавычки, а если оно содержит запятые или скобки, это делать обязательно. (Подробности описаны ниже.) Таким образом, в общем виде константа составного типа записывается так:
'( значение1 , значение2 , ... )'
Например, эта запись:
'("fuzzy dice",42,1.99)'будет допустимой для описанного выше типа inventory_item. Чтобы присвоить NULL одному из полей, в соответствующем месте в списке нужно оставить пустое место. Например, эта константа задаёт значение для третьего поля:
'("fuzzy dice",42,)'Если же вместо NULL требуется вставить пустую строку, нужно записать пару кавычек:
'("",42,)'Здесь в первом поле окажется пустая строка, а в третьем — 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;
Но это не будет работать, так как согласно правилам 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 ...
Без дополнительных скобок в этом запросе произойдёт синтаксическая ошибка.
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. Синтаксис вводимых и выводимых значений составного типа
Внешнее текстовое представление составного значения состоит из записи элементов, интерпретируемых по правилам ввода/вывода для соответствующих типов полей, и оформления структуры составного типа. Оформление состоит из круглых скобок (( и )) окружающих всё значение, и запятых (,) между его элементами. Пробельные символы вне скобок игнорируются, но внутри они считаются частью соответствующего элемента и могут учитываться или не учитываться в зависимости от правил преобразования вводимых данных для типа этого элемента. Например, в записи:
'( 42)'
пробелы будут игнорироваться, если соответствующее поле имеет целочисленный тип, но не текстовый.
Как было показано ранее, записывая составное значение, любой его элемент можно заключить в кавычки. Это нужно делать, если при разборе этого значения без кавычек возможна неоднозначность. Например, в кавычки нужно заключать элементы, содержащие скобки, кавычки, запятую или обратную косую черту. Чтобы включить в поле составного значения, заключённое в кавычки, такие символы, как кавычки или обратная косая черта, перед ними нужно добавить обратную косую черту. (Кроме того, продублированные кавычки в значении поля, заключённого в кавычки, воспринимаются как одинарные, подобно апострофам в строках SQL.) С другой стороны, можно обойтись без кавычек, защитив все символы в данных, которые могут быть восприняты как часть синтаксиса составного значения, с помощью спецпоследовательностей.
Значение NULL в этой записи представляется пустым местом (когда между запятыми или скобками нет никаких символов). Чтобы ввести именно пустую строку, а не NULL, нужно написать "".
Функция вывода составного значения заключает значения полей в кавычки, если они представляют собой пустые строки, либо содержат скобки, запятые, кавычки или обратную косую черту, либо состоят из одних пробелов. (В последнем случае можно обойтись без кавычек, но они добавляются для удобочитаемости.) Кавычки и обратная косая черта, заключённые в значения полей, при выводе дублируются.
Замечание: Помните, что написанная SQL-команда прежде всего интерпретируется как текстовая строка, а затем как составное значение. Вследствие этого число символов обратной косой черты удваивается (если используются спецпоследовательности). Например, чтобы ввести в поле составной колонки значение типа text с обратной косой чертой и кавычками, команду нужно будет записать так:
INSERT ... VALUES (E'("\\"\\\\")');Сначала обработчик спецпоследовательностей удаляет один уровень обратной косой черты, так что анализатор составного значения получает на вход ("\"\\"). В свою очередь, он передаёт эту строку процедуре ввода значения типа text, где она преобразуются в "\. (Если бы мы работали с типом данных, процедура ввода которого также интерпретирует обратную косую черту особым образом, например bytea, нам могло бы понадобиться уже восемь таких символов, чтобы сохранить этот символ в поле составного значения.) Во избежание такого дублирования спецсимволов строки можно заключать в доллары (см. Подраздел 4.1.2.4).
Подсказка: Записывать составные значения в командах SQL часто бывает удобнее с помощью конструктора ROW. В ROW отдельные значения элементов записываются так же, как если бы они не были членами составного выражения.
| Пред. | Начало | След. |
| Массивы | Уровень выше | Диапазонные типы |
| PostgreSQL 9.4.1 Documentation | |||
|---|---|---|---|
| Prev | Up | Chapter 8. Data Types | Next |
8.16. Composite Types
A composite type represents the structure of a row or record; it is essentially just a list of field names and their data types. PostgreSQL allows composite types to be used in many of the same ways that simple types can be used. For example, a column of a table can be declared to be of a composite type.
8.16.1. Declaration of Composite Types
Here are two simple examples of defining composite types:
CREATE TYPE complex AS (
r double precision,
i double precision
);
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);The syntax is comparable to CREATE TABLE, except that only field names and types can be specified; no constraints (such as NOT NULL) can presently be included. Note that the AS keyword is essential; without it, the system will think a different kind of CREATE TYPE command is meant, and you will get odd syntax errors.
Having defined the types, we can use them to create tables:
CREATE TABLE on_hand (
item inventory_item,
count integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);or functions:
CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric AS 'SELECT $1.price * $2' LANGUAGE SQL; SELECT price_extension(item, 10) FROM on_hand;
Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type. For example, had we said:
CREATE TABLE inventory_item (
name text,
supplier_id integer REFERENCES suppliers,
price numeric CHECK (price > 0)
);then the same inventory_item composite type shown above would come into being as a byproduct, and could be used just as above. Note however an important restriction of the current implementation: since no constraints are associated with a composite type, the constraints shown in the table definition do not apply to values of the composite type outside the table. (A partial workaround is to use domain types as members of composite types.)
8.16.2. Composite Value Input
To write a composite value as a literal constant, enclose the field values within parentheses and separate them by commas. You can put double quotes around any field value, and must do so if it contains commas or parentheses. (More details appear below.) Thus, the general format of a composite constant is the following:
'( val1 , val2 , ... )'
An example is:
'("fuzzy dice",42,1.99)'which would be a valid value of the inventory_item type defined above. To make a field be NULL, write no characters at all in its position in the list. For example, this constant specifies a NULL third field:
'("fuzzy dice",42,)'If you want an empty string rather than NULL, write double quotes:
'("",42,)'Here the first field is a non-NULL empty string, the third is NULL.
(These constants are actually only a special case of the generic type constants discussed in Section 4.1.2.7. The constant is initially treated as a string and passed to the composite-type input conversion routine. An explicit type specification might be necessary.)
The ROW expression syntax can also be used to construct composite values. In most cases this is considerably simpler to use than the string-literal syntax since you don't have to worry about multiple layers of quoting. We already used this method above:
ROW('fuzzy dice', 42, 1.99)
ROW('', 42, NULL)The ROW keyword is actually optional as long as you have more than one field in the expression, so these can simplify to:
('fuzzy dice', 42, 1.99)
('', 42, NULL)The ROW expression syntax is discussed in more detail in Section 4.2.13.
8.16.3. Accessing Composite Types
To access a field of a composite column, one writes a dot and the field name, much like selecting a field from a table name. In fact, it's so much like selecting from a table name that you often have to use parentheses to keep from confusing the parser. For example, you might try to select some subfields from our on_hand example table with something like:
SELECT item.name FROM on_hand WHERE item.price > 9.99;
This will not work since the name item is taken to be a table name, not a column name of on_hand, per SQL syntax rules. You must write it like this:
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
or if you need to use the table name as well (for instance in a multitable query), like this:
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
Now the parenthesized object is correctly interpreted as a reference to the item column, and then the subfield can be selected from it.
Similar syntactic issues apply whenever you select a field from a composite value. For instance, to select just one field from the result of a function that returns a composite value, you'd need to write something like:
SELECT (my_func(...)).field FROM ...
Without the extra parentheses, this will generate a syntax error.
8.16.4. Modifying Composite Types
Here are some examples of the proper syntax for inserting and updating composite columns. First, inserting or updating a whole column:
INSERT INTO mytab (complex_col) VALUES((1.1,2.2)); UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;
The first example omits ROW, the second uses it; we could have done it either way.
We can update an individual subfield of a composite column:
UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;
Notice here that we don't need to (and indeed cannot) put parentheses around the column name appearing just after SET, but we do need parentheses when referencing the same column in the expression to the right of the equal sign.
And we can specify subfields as targets for INSERT, too:
INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);
Had we not supplied values for all the subfields of the column, the remaining subfields would have been filled with null values.
8.16.5. Composite Type Input and Output Syntax
The external text representation of a composite value consists of items that are interpreted according to the I/O conversion rules for the individual field types, plus decoration that indicates the composite structure. The decoration consists of parentheses (( and )) around the whole value, plus commas (,) between adjacent items. Whitespace outside the parentheses is ignored, but within the parentheses it is considered part of the field value, and might or might not be significant depending on the input conversion rules for the field data type. For example, in:
'( 42)'
the whitespace will be ignored if the field type is integer, but not if it is text.
As shown previously, when writing a composite value you can write double quotes around any individual field value. You must do so if the field value would otherwise confuse the composite-value parser. In particular, fields containing parentheses, commas, double quotes, or backslashes must be double-quoted. To put a double quote or backslash in a quoted composite field value, precede it with a backslash. (Also, a pair of double quotes within a double-quoted field value is taken to represent a double quote character, analogously to the rules for single quotes in SQL literal strings.) Alternatively, you can avoid quoting and use backslash-escaping to protect all data characters that would otherwise be taken as composite syntax.
A completely empty field value (no characters at all between the commas or parentheses) represents a NULL. To write a value that is an empty string rather than NULL, write "".
The composite output routine will put double quotes around field values if they are empty strings or contain parentheses, commas, double quotes, backslashes, or white space. (Doing so for white space is not essential, but aids legibility.) Double quotes and backslashes embedded in field values will be doubled.
Note: Remember that what you write in an SQL command will first be interpreted as a string literal, and then as a composite. This doubles the number of backslashes you need (assuming escape string syntax is used). For example, to insert a text field containing a double quote and a backslash in a composite value, you'd need to write:
INSERT ... VALUES (E'("\\"\\\\")');The string-literal processor removes one level of backslashes, so that what arrives at the composite-value parser looks like ("\"\\"). In turn, the string fed to the text data type's input routine becomes "\. (If we were working with a data type whose input routine also treated backslashes specially, bytea for example, we might need as many as eight backslashes in the command to get one backslash into the stored composite field.) Dollar quoting (see Section 4.1.2.4) can be used to avoid the need to double backslashes.
Tip: The ROW constructor syntax is usually easier to work with than the composite-literal syntax when writing composite values in SQL commands. In ROW, individual field values are written the same way they would be written when not members of a composite.