| 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 > 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 > 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 > 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 > 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 > 1000; |
| 411 |
SELECT name(c) FROM inventory_item c WHERE price(c) > 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> |