4
<title>Composite Types</title>
7
<primary>composite type</primary>
11
<primary>row type</primary>
15
A <firstterm>composite type</> describes the structure of a row or record;
16
it is in essence just a list of field names and their data types.
17
<productname>PostgreSQL</productname> allows values of composite types to be
18
used in many of the same ways that simple types can be used. For example, a
19
column of a table can be declared to be of a composite type.
23
<title>Declaration of Composite Types</title>
26
Here are two simple examples of defining composite types:
28
CREATE TYPE complex AS (
33
CREATE TYPE inventory_item AS (
39
The syntax is comparable to <command>CREATE TABLE</>, except that only
40
field names and types can be specified; no constraints (such as <literal>NOT
41
NULL</>) can presently be included. Note that the <literal>AS</> keyword
42
is essential; without it, the system will think a quite different kind
43
of <command>CREATE TYPE</> command is meant, and you'll get odd syntax
48
Having defined the types, we can use them to create tables:
51
CREATE TABLE on_hand (
56
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
62
CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
63
AS 'SELECT $1.price * $2' LANGUAGE SQL;
65
SELECT price_extension(item, 10) FROM on_hand;
71
Whenever you create a table, a composite type is also automatically
72
created, with the same name as the table, to represent the table's
73
row type. For example, had we said:
75
CREATE TABLE inventory_item (
77
supplier_id integer REFERENCES suppliers,
78
price numeric CHECK (price > 0)
81
then the same <literal>inventory_item</> composite type shown above would
83
byproduct, and could be used just as above. Note however an important
84
restriction of the current implementation: since no constraints are
85
associated with a composite type, the constraints shown in the table
86
definition <emphasis>do not apply</> to values of the composite type
87
outside the table. (A partial workaround is to use domain
88
types as members of composite types.)
93
<title>Composite Value Input</title>
96
<primary>composite type</primary>
97
<secondary>constant</secondary>
101
To write a composite value as a literal constant, enclose the field
102
values within parentheses and separate them by commas. You can put double
103
quotes around any field value, and must do so if it contains commas or
104
parentheses. (More details appear below.) Thus, the general format of a
105
composite constant is the following:
107
'( <replaceable>val1</replaceable> , <replaceable>val2</replaceable> , ... )'
111
'("fuzzy dice",42,1.99)'
113
which would be a valid value of the <literal>inventory_item</> type
114
defined above. To make a field be NULL, write no characters at all
115
in its position in the list. For example, this constant specifies
120
If you want an empty string rather than NULL, write double quotes:
124
Here the first field is a non-NULL empty string, the third is NULL.
128
(These constants are actually only a special case of
129
the generic type constants discussed in <xref
130
linkend="sql-syntax-constants-generic">. The constant is initially
131
treated as a string and passed to the composite-type input conversion
132
routine. An explicit type specification might be necessary.)
136
The <literal>ROW</literal> expression syntax can also be used to
137
construct composite values. In most cases this is considerably
138
simpler to use than the string-literal syntax, since you don't have
139
to worry about multiple layers of quoting. We already used this
142
ROW('fuzzy dice', 42, 1.99)
145
The ROW keyword is actually optional as long as you have more than one
146
field in the expression, so these can simplify to:
148
('fuzzy dice', 42, 1.99)
151
The <literal>ROW</> expression syntax is discussed in more detail in <xref
152
linkend="sql-syntax-row-constructors">.
157
<title>Accessing Composite Types</title>
160
To access a field of a composite column, one writes a dot and the field
161
name, much like selecting a field from a table name. In fact, it's so
162
much like selecting from a table name that you often have to use parentheses
163
to keep from confusing the parser. For example, you might try to select
164
some subfields from our <literal>on_hand</> example table with something
168
SELECT item.name FROM on_hand WHERE item.price > 9.99;
171
This will not work since the name <literal>item</> is taken to be a table
172
name, not a field name, per SQL syntax rules. You must write it like this:
175
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
178
or if you need to use the table name as well (for instance in a multitable
182
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
185
Now the parenthesized object is correctly interpreted as a reference to
186
the <literal>item</> column, and then the subfield can be selected from it.
190
Similar syntactic issues apply whenever you select a field from a composite
191
value. For instance, to select just one field from the result of a function
192
that returns a composite value, you'd need to write something like:
195
SELECT (my_func(...)).field FROM ...
198
Without the extra parentheses, this will provoke a syntax error.
203
<title>Modifying Composite Types</title>
206
Here are some examples of the proper syntax for inserting and updating
208
First, inserting or updating a whole column:
211
INSERT INTO mytab (complex_col) VALUES((1.1,2.2));
213
UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;
216
The first example omits <literal>ROW</>, the second uses it; we
217
could have done it either way.
221
We can update an individual subfield of a composite column:
224
UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;
227
Notice here that we don't need to (and indeed cannot)
228
put parentheses around the column name appearing just after
229
<literal>SET</>, but we do need parentheses when referencing the same
230
column in the expression to the right of the equal sign.
234
And we can specify subfields as targets for <command>INSERT</>, too:
237
INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);
240
Had we not supplied values for all the subfields of the column, the
241
remaining subfields would have been filled with null values.
246
<title>Composite Type Input and Output Syntax</title>
249
The external text representation of a composite value consists of items that
250
are interpreted according to the I/O conversion rules for the individual
251
field types, plus decoration that indicates the composite structure.
252
The decoration consists of parentheses (<literal>(</> and <literal>)</>)
253
around the whole value, plus commas (<literal>,</>) between adjacent
254
items. Whitespace outside the parentheses is ignored, but within the
255
parentheses it is considered part of the field value, and might or might not be
256
significant depending on the input conversion rules for the field data type.
261
the whitespace will be ignored if the field type is integer, but not if
266
As shown previously, when writing a composite value you can write double
267
quotes around any individual field value.
268
You <emphasis>must</> do so if the field value would otherwise
269
confuse the composite-value parser. In particular, fields containing
270
parentheses, commas, double quotes, or backslashes must be double-quoted.
271
To put a double quote or backslash in a quoted composite field value,
272
precede it with a backslash. (Also, a pair of double quotes within a
273
double-quoted field value is taken to represent a double quote character,
274
analogously to the rules for single quotes in SQL literal strings.)
275
Alternatively, you can use backslash-escaping to protect all data characters
276
that would otherwise be taken as composite syntax.
280
A completely empty field value (no characters at all between the commas
281
or parentheses) represents a NULL. To write a value that is an empty
282
string rather than NULL, write <literal>""</>.
286
The composite output routine will put double quotes around field values
287
if they are empty strings or contain parentheses, commas,
288
double quotes, backslashes, or white space. (Doing so for white space
289
is not essential, but aids legibility.) Double quotes and backslashes
290
embedded in field values will be doubled.
295
Remember that what you write in an SQL command will first be interpreted
296
as a string literal, and then as a composite. This doubles the number of
297
backslashes you need (assuming escape string syntax is used).
298
For example, to insert a <type>text</> field
299
containing a double quote and a backslash in a composite
300
value, you'd need to write:
302
INSERT ... VALUES (E'("\\"\\\\")');
304
The string-literal processor removes one level of backslashes, so that
305
what arrives at the composite-value parser looks like
306
<literal>("\"\\")</>. In turn, the string
307
fed to the <type>text</> data type's input routine
308
becomes <literal>"\</>. (If we were working
309
with a data type whose input routine also treated backslashes specially,
310
<type>bytea</> for example, we might need as many as eight backslashes
311
in the command to get one backslash into the stored composite field.)
312
Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting">) can be
313
used to avoid the need to double backslashes.
319
The <literal>ROW</> constructor syntax is usually easier to work with
320
than the composite-literal syntax when writing composite values in SQL
322
In <literal>ROW</>, individual field values are written the same way
323
they would be written when not members of a composite.