Debian Codegraph @

doc/src/sgml/rowtypes.sgml

raw
1 <!-- doc/src/sgml/rowtypes.sgml -->
2
3 <sect1 id="rowtypes">
4 <title>Composite Types</title>
5
6 <indexterm>
7 <primary>composite type</primary>
8 </indexterm>
9
10 <indexterm>
11 <primary>row type</primary>
12 </indexterm>
13
14 <para>
15 A <firstterm>composite type</firstterm> represents the structure of a row or record;
16 it is essentially just a list of field names and their data types.
17 <productname>PostgreSQL</productname> allows 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.
20 </para>
21
22 <sect2 id="rowtypes-declaring">
23 <title>Declaration of Composite Types</title>
24
25 <para>
26 Here are two simple examples of defining composite types:
27 <programlisting>
28 CREATE TYPE complex AS (
29 r double precision,
30 i double precision
31 );
32
33 CREATE TYPE inventory_item AS (
34 name text,
35 supplier_id integer,
36 price numeric
37 );
38 </programlisting>
39 The syntax is comparable to <command>CREATE TABLE</command>, except that only
40 field names and types can be specified; no constraints (such as <literal>NOT
41 NULL</literal>) can presently be included. Note that the <literal>AS</literal> keyword
42 is essential; without it, the system will think a different kind
43 of <command>CREATE TYPE</command> command is meant, and you will get odd syntax
44 errors.
45 </para>
46
47 <para>
48 Having defined the types, we can use them to create tables:
49
50 <programlisting>
51 CREATE TABLE on_hand (
52 item inventory_item,
53 count integer
54 );
55
56 INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
57 </programlisting>
58
59 or functions:
60
61 <programlisting>
62 CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
63 AS 'SELECT $1.price * $2' LANGUAGE SQL;
64
65 SELECT price_extension(item, 10) FROM on_hand;
66 </programlisting>
67
68 </para>
69
70 <para>
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:
74 <programlisting>
75 CREATE TABLE inventory_item (
76 name text,
77 supplier_id integer REFERENCES suppliers,
78 price numeric CHECK (price &gt; 0)
79 );
80 </programlisting>
81 then the same <literal>inventory_item</literal> composite type shown above would
82 come into being as a
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</emphasis> to values of the composite type
87 outside the table. (To work around this, create a
88 <glossterm linkend="glossary-domain">domain</glossterm> over the composite
89 type, and apply the desired constraints as <literal>CHECK</literal>
90 constraints of the domain.)
91 </para>
92 </sect2>
93
94 <sect2 id="rowtypes-constructing">
95 <title>Constructing Composite Values</title>
96
97 <indexterm>
98 <primary>composite type</primary>
99 <secondary>constant</secondary>
100 </indexterm>
101
102 <para>
103 To write a composite value as a literal constant, enclose the field
104 values within parentheses and separate them by commas. You can put double
105 quotes around any field value, and must do so if it contains commas or
106 parentheses. (More details appear <link
107 linkend="rowtypes-io-syntax">below</link>.) Thus, the general format of
108 a composite constant is the following:
109 <synopsis>
110 '( <replaceable>val1</replaceable> , <replaceable>val2</replaceable> , ... )'
111 </synopsis>
112 An example is:
113 <programlisting>
114 '("fuzzy dice",42,1.99)'
115 </programlisting>
116 which would be a valid value of the <literal>inventory_item</literal> type
117 defined above. To make a field be NULL, write no characters at all
118 in its position in the list. For example, this constant specifies
119 a NULL third field:
120 <programlisting>
121 '("fuzzy dice",42,)'
122 </programlisting>
123 If you want an empty string rather than NULL, write double quotes:
124 <programlisting>
125 '("",42,)'
126 </programlisting>
127 Here the first field is a non-NULL empty string, the third is NULL.
128 </para>
129
130 <para>
131 (These constants are actually only a special case of
132 the generic type constants discussed in <xref
133 linkend="sql-syntax-constants-generic"/>. The constant is initially
134 treated as a string and passed to the composite-type input conversion
135 routine. An explicit type specification might be necessary to tell
136 which type to convert the constant to.)
137 </para>
138
139 <para>
140 The <literal>ROW</literal> expression syntax can also be used to
141 construct composite values. In most cases this is considerably
142 simpler to use than the string-literal syntax since you don't have
143 to worry about multiple layers of quoting. We already used this
144 method above:
145 <programlisting>
146 ROW('fuzzy dice', 42, 1.99)
147 ROW('', 42, NULL)
148 </programlisting>
149 The ROW keyword is actually optional as long as you have more than one
150 field in the expression, so these can be simplified to:
151 <programlisting>
152 ('fuzzy dice', 42, 1.99)
153 ('', 42, NULL)
154 </programlisting>
155 The <literal>ROW</literal> expression syntax is discussed in more detail in <xref
156 linkend="sql-syntax-row-constructors"/>.
157 </para>
158 </sect2>
159
160 <sect2 id="rowtypes-accessing">
161 <title>Accessing Composite Types</title>
162
163 <para>
164 To access a field of a composite column, one writes a dot and the field
165 name, much like selecting a field from a table name. In fact, it's so
166 much like selecting from a table name that you often have to use parentheses
167 to keep from confusing the parser. For example, you might try to select
168 some subfields from our <literal>on_hand</literal> example table with something
169 like:
170
171 <programlisting>
172 SELECT item.name FROM on_hand WHERE item.price &gt; 9.99;
173 </programlisting>
174
175 This will not work since the name <literal>item</literal> is taken to be a table
176 name, not a column name of <literal>on_hand</literal>, per SQL syntax rules.
177 You must write it like this:
178
179 <programlisting>
180 SELECT (item).name FROM on_hand WHERE (item).price &gt; 9.99;
181 </programlisting>
182
183 or if you need to use the table name as well (for instance in a multitable
184 query), like this:
185
186 <programlisting>
187 SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price &gt; 9.99;
188 </programlisting>
189
190 Now the parenthesized object is correctly interpreted as a reference to
191 the <literal>item</literal> column, and then the subfield can be selected from it.
192 </para>
193
194 <para>
195 Similar syntactic issues apply whenever you select a field from a composite
196 value. For instance, to select just one field from the result of a function
197 that returns a composite value, you'd need to write something like:
198
199 <programlisting>
200 SELECT (my_func(...)).field FROM ...
201 </programlisting>
202
203 Without the extra parentheses, this will generate a syntax error.
204 </para>
205
206 <para>
207 The special field name <literal>*</literal> means <quote>all fields</quote>, as
208 further explained in <xref linkend="rowtypes-usage"/>.
209 </para>
210 </sect2>
211
212 <sect2 id="rowtypes-modifying">
213 <title>Modifying Composite Types</title>
214
215 <para>
216 Here are some examples of the proper syntax for inserting and updating
217 composite columns.
218 First, inserting or updating a whole column:
219
220 <programlisting>
221 INSERT INTO mytab (complex_col) VALUES((1.1,2.2));
222
223 UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;
224 </programlisting>
225
226 The first example omits <literal>ROW</literal>, the second uses it; we
227 could have done it either way.
228 </para>
229
230 <para>
231 We can update an individual subfield of a composite column:
232
233 <programlisting>
234 UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;
235 </programlisting>
236
237 Notice here that we don't need to (and indeed cannot)
238 put parentheses around the column name appearing just after
239 <literal>SET</literal>, but we do need parentheses when referencing the same
240 column in the expression to the right of the equal sign.
241 </para>
242
243 <para>
244 And we can specify subfields as targets for <command>INSERT</command>, too:
245
246 <programlisting>
247 INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);
248 </programlisting>
249
250 Had we not supplied values for all the subfields of the column, the
251 remaining subfields would have been filled with null values.
252 </para>
253 </sect2>
254
255 <sect2 id="rowtypes-usage">
256 <title>Using Composite Types in Queries</title>
257
258 <para>
259 There are various special syntax rules and behaviors associated with
260 composite types in queries. These rules provide useful shortcuts,
261 but can be confusing if you don't know the logic behind them.
262 </para>
263
264 <para>
265 In <productname>PostgreSQL</productname>, a reference to a table name (or alias)
266 in a query is effectively a reference to the composite value of the
267 table's current row. For example, if we had a table
268 <structname>inventory_item</structname> as shown
269 <link linkend="rowtypes-declaring">above</link>, we could write:
270 <programlisting>
271 SELECT c FROM inventory_item c;
272 </programlisting>
273 This query produces a single composite-valued column, so we might get
274 output like:
275 <programlisting>
276 c
277 ------------------------
278 ("fuzzy dice",42,1.99)
279 (1 row)
280 </programlisting>
281 Note however that simple names are matched to column names before table
282 names, so this example works only because there is no column
283 named <structfield>c</structfield> in the query's tables.
284 </para>
285
286 <para>
287 The ordinary qualified-column-name
288 syntax <replaceable>table_name</replaceable><literal>.</literal><replaceable>column_name</replaceable>
289 can be understood as applying <link linkend="field-selection">field
290 selection</link> to the composite value of the table's current row.
291 (For efficiency reasons, it's not actually implemented that way.)
292 </para>
293
294 <para>
295 When we write
296 <programlisting>
297 SELECT c.* FROM inventory_item c;
298 </programlisting>
299 then, according to the SQL standard, we should get the contents of the
300 table expanded into separate columns:
301 <programlisting>
302 name | supplier_id | price
303 ------------+-------------+-------
304 fuzzy dice | 42 | 1.99
305 (1 row)
306 </programlisting>
307 as if the query were
308 <programlisting>
309 SELECT c.name, c.supplier_id, c.price FROM inventory_item c;
310 </programlisting>
311 <productname>PostgreSQL</productname> will apply this expansion behavior to
312 any composite-valued expression, although as shown <link
313 linkend="rowtypes-accessing">above</link>, you need to write parentheses
314 around the value that <literal>.*</literal> is applied to whenever it's not a
315 simple table name. For example, if <function>myfunc()</function> is a function
316 returning a composite type with columns <structfield>a</structfield>,
317 <structfield>b</structfield>, and <structfield>c</structfield>, then these two queries have the
318 same result:
319 <programlisting>
320 SELECT (myfunc(x)).* FROM some_table;
321 SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;
322 </programlisting>
323 </para>
324
325 <tip>
326 <para>
327 <productname>PostgreSQL</productname> handles column expansion by
328 actually transforming the first form into the second. So, in this
329 example, <function>myfunc()</function> would get invoked three times per row
330 with either syntax. If it's an expensive function you may wish to
331 avoid that, which you can do with a query like:
332 <programlisting>
333 SELECT m.* FROM some_table, LATERAL myfunc(x) AS m;
334 </programlisting>
335 Placing the function in
336 a <literal>LATERAL</literal> <literal>FROM</literal> item keeps it from
337 being invoked more than once per row. <literal>m.*</literal> is still
338 expanded into <literal>m.a, m.b, m.c</literal>, but now those variables
339 are just references to the output of the <literal>FROM</literal> item.
340 (The <literal>LATERAL</literal> keyword is optional here, but we show it
341 to clarify that the function is getting <structfield>x</structfield>
342 from <structname>some_table</structname>.)
343 </para>
344 </tip>
345
346 <para>
347 The <replaceable>composite_value</replaceable><literal>.*</literal> syntax results in
348 column expansion of this kind when it appears at the top level of
349 a <link linkend="queries-select-lists"><command>SELECT</command> output
350 list</link>, a <link linkend="dml-returning"><literal>RETURNING</literal>
351 list</link> in <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>/<command>MERGE</command>,
352 a <link linkend="queries-values"><literal>VALUES</literal> clause</link>, or
353 a <link linkend="sql-syntax-row-constructors">row constructor</link>.
354 In all other contexts (including when nested inside one of those
355 constructs), attaching <literal>.*</literal> to a composite value does not
356 change the value, since it means <quote>all columns</quote> and so the
357 same composite value is produced again. For example,
358 if <function>somefunc()</function> accepts a composite-valued argument,
359 these queries are the same:
360
361 <programlisting>
362 SELECT somefunc(c.*) FROM inventory_item c;
363 SELECT somefunc(c) FROM inventory_item c;
364 </programlisting>
365
366 In both cases, the current row of <structname>inventory_item</structname> is
367 passed to the function as a single composite-valued argument.
368 Even though <literal>.*</literal> does nothing in such cases, using it is good
369 style, since it makes clear that a composite value is intended. In
370 particular, the parser will consider <literal>c</literal> in <literal>c.*</literal> to
371 refer to a table name or alias, not to a column name, so that there is
372 no ambiguity; whereas without <literal>.*</literal>, it is not clear
373 whether <literal>c</literal> means a table name or a column name, and in fact
374 the column-name interpretation will be preferred if there is a column
375 named <literal>c</literal>.
376 </para>
377
378 <para>
379 Another example demonstrating these concepts is that all these queries
380 mean the same thing:
381 <programlisting>
382 SELECT * FROM inventory_item c ORDER BY c;
383 SELECT * FROM inventory_item c ORDER BY c.*;
384 SELECT * FROM inventory_item c ORDER BY ROW(c.*);
385 </programlisting>
386 All of these <literal>ORDER BY</literal> clauses specify the row's composite
387 value, resulting in sorting the rows according to the rules described
388 in <xref linkend="composite-type-comparison"/>. However,
389 if <structname>inventory_item</structname> contained a column
390 named <structfield>c</structfield>, the first case would be different from the
391 others, as it would mean to sort by that column only. Given the column
392 names previously shown, these queries are also equivalent to those above:
393 <programlisting>
394 SELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price);
395 SELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price);
396 </programlisting>
397 (The last case uses a row constructor with the key word <literal>ROW</literal>
398 omitted.)
399 </para>
400
401 <para>
402 Another special syntactical behavior associated with composite values is
403 that we can use <firstterm>functional notation</firstterm> for extracting a field
404 of a composite value. The simple way to explain this is that
405 the notations <literal><replaceable>field</replaceable>(<replaceable>table</replaceable>)</literal>
406 and <literal><replaceable>table</replaceable>.<replaceable>field</replaceable></literal>
407 are interchangeable. For example, these queries are equivalent:
408
409 <programlisting>
410 SELECT c.name FROM inventory_item c WHERE c.price &gt; 1000;
411 SELECT name(c) FROM inventory_item c WHERE price(c) &gt; 1000;
412 </programlisting>
413
414 Moreover, if we have a function that accepts a single argument of a
415 composite type, we can call it with either notation. These queries are
416 all equivalent:
417
418 <programlisting>
419 SELECT somefunc(c) FROM inventory_item c;
420 SELECT somefunc(c.*) FROM inventory_item c;
421 SELECT c.somefunc FROM inventory_item c;
422 </programlisting>
423 </para>
424
425 <para>
426 This equivalence between functional notation and field notation
427 makes it possible to use functions on composite types to implement
428 <quote>computed fields</quote>.
429 <indexterm>
430 <primary>computed field</primary>
431 </indexterm>
432 <indexterm>
433 <primary>field</primary>
434 <secondary>computed</secondary>
435 </indexterm>
436 An application using the last query above wouldn't need to be directly
437 aware that <literal>somefunc</literal> isn't a real column of the table.
438 </para>
439
440 <tip>
441 <para>
442 Because of this behavior, it's unwise to give a function that takes a
443 single composite-type argument the same name as any of the fields of
444 that composite type. If there is ambiguity, the field-name
445 interpretation will be chosen if field-name syntax is used, while the
446 function will be chosen if function-call syntax is used. However,
447 <productname>PostgreSQL</productname> versions before 11 always chose the
448 field-name interpretation, unless the syntax of the call required it to
449 be a function call. One way to force the function interpretation in
450 older versions is to schema-qualify the function name, that is, write
451 <literal><replaceable>schema</replaceable>.<replaceable>func</replaceable>(<replaceable>compositevalue</replaceable>)</literal>.
452 </para>
453 </tip>
454 </sect2>
455
456 <sect2 id="rowtypes-io-syntax">
457 <title>Composite Type Input and Output Syntax</title>
458
459 <para>
460 The external text representation of a composite value consists of items that
461 are interpreted according to the I/O conversion rules for the individual
462 field types, plus decoration that indicates the composite structure.
463 The decoration consists of parentheses (<literal>(</literal> and <literal>)</literal>)
464 around the whole value, plus commas (<literal>,</literal>) between adjacent
465 items. Whitespace outside the parentheses is ignored, but within the
466 parentheses it is considered part of the field value, and might or might not be
467 significant depending on the input conversion rules for the field data type.
468 For example, in:
469 <programlisting>
470 '( 42)'
471 </programlisting>
472 the whitespace will be ignored if the field type is integer, but not if
473 it is text.
474 </para>
475
476 <para>
477 As shown previously, when writing a composite value you can write double
478 quotes around any individual field value.
479 You <emphasis>must</emphasis> do so if the field value would otherwise
480 confuse the composite-value parser. In particular, fields containing
481 parentheses, commas, double quotes, or backslashes must be double-quoted.
482 To put a double quote or backslash in a quoted composite field value,
483 precede it with a backslash. (Also, a pair of double quotes within a
484 double-quoted field value is taken to represent a double quote character,
485 analogously to the rules for single quotes in SQL literal strings.)
486 Alternatively, you can avoid quoting and use backslash-escaping to
487 protect all data characters
488 that would otherwise be taken as composite syntax.
489 </para>
490
491 <para>
492 A completely empty field value (no characters at all between the commas
493 or parentheses) represents a NULL. To write a value that is an empty
494 string rather than NULL, write <literal>""</literal>.
495 </para>
496
497 <para>
498 The composite output routine will put double quotes around field values
499 if they are empty strings or contain parentheses, commas,
500 double quotes, backslashes, or white space. (Doing so for white space
501 is not essential, but aids legibility.) Double quotes and backslashes
502 embedded in field values will be doubled.
503 </para>
504
505 <note>
506 <para>
507 Remember that what you write in an SQL command will first be interpreted
508 as a string literal, and then as a composite. This doubles the number of
509 backslashes you need (assuming escape string syntax is used).
510 For example, to insert a <type>text</type> field
511 containing a double quote and a backslash in a composite
512 value, you'd need to write:
513 <programlisting>
514 INSERT ... VALUES ('("\"\\")');
515 </programlisting>
516 The string-literal processor removes one level of backslashes, so that
517 what arrives at the composite-value parser looks like
518 <literal>("\"\\")</literal>. In turn, the string
519 fed to the <type>text</type> data type's input routine
520 becomes <literal>"\</literal>. (If we were working
521 with a data type whose input routine also treated backslashes specially,
522 <type>bytea</type> for example, we might need as many as eight backslashes
523 in the command to get one backslash into the stored composite field.)
524 Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting"/>) can be
525 used to avoid the need to double backslashes.
526 </para>
527 </note>
528
529 <tip>
530 <para>
531 The <literal>ROW</literal> constructor syntax is usually easier to work with
532 than the composite-literal syntax when writing composite values in SQL
533 commands.
534 In <literal>ROW</literal>, individual field values are written the same way
535 they would be written when not members of a composite.
536 </para>
537 </tip>
538 </sect2>
539
540 </sect1>