doc/src/sgml/rowtypes.sgml

   1 <!-- $PostgreSQL$ -->
   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</> 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>
  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</>, 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 different kind
  43   of <command>CREATE TYPE</> 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</> 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</> to values of the composite type
  87   outside the table.  (A partial workaround is to use domain
  88   types as members of composite types.)
  89  </para>
  90  </sect2>
  91
  92  <sect2>
  93   <title>Composite Value Input</title>
  94
  95   <indexterm>
  96    <primary>composite type</primary>
  97    <secondary>constant</secondary>
  98   </indexterm>
  99
 100   <para>
 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:
 106 <synopsis>
 107 '( <replaceable>val1</replaceable> , <replaceable>val2</replaceable> , ... )'
 108 </synopsis>
 109    An example is:
 110 <programlisting>
 111 '("fuzzy dice",42,1.99)'
 112 </programlisting>
 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
 116    a NULL third field:
 117 <programlisting>
 118 '("fuzzy dice",42,)'
 119 </programlisting>
 120    If you want an empty string rather than NULL, write double quotes:
 121 <programlisting>
 122 '("",42,)'
 123 </programlisting>
 124    Here the first field is a non-NULL empty string, the third is NULL.
 125   </para>
 126
 127   <para>
 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.)
 133   </para>
 134
 135  <para>
 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
 140   method above:
 141 <programlisting>
 142 ROW('fuzzy dice', 42, 1.99)
 143 ROW('', 42, NULL)
 144 </programlisting>
 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:
 147 <programlisting>
 148 ('fuzzy dice', 42, 1.99)
 149 ('', 42, NULL)
 150 </programlisting>
 151   The <literal>ROW</> expression syntax is discussed in more detail in <xref
 152   linkend="sql-syntax-row-constructors">.
 153  </para>
 154  </sect2>
 155
 156  <sect2>
 157   <title>Accessing Composite Types</title>
 158
 159  <para>
 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
 165   like:
 166
 167 <programlisting>
 168 SELECT item.name FROM on_hand WHERE item.price &gt; 9.99;
 169 </programlisting>
 170
 171   This will not work since the name <literal>item</> is taken to be a table
 172   name, not a column name of <literal>on_hand</>, per SQL syntax rules.
 173   You must write it like this:
 174
 175 <programlisting>
 176 SELECT (item).name FROM on_hand WHERE (item).price &gt; 9.99;
 177 </programlisting>
 178
 179   or if you need to use the table name as well (for instance in a multitable
 180   query), like this:
 181
 182 <programlisting>
 183 SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price &gt; 9.99;
 184 </programlisting>
 185
 186   Now the parenthesized object is correctly interpreted as a reference to
 187   the <literal>item</> column, and then the subfield can be selected from it.
 188  </para>
 189
 190  <para>
 191   Similar syntactic issues apply whenever you select a field from a composite
 192   value.  For instance, to select just one field from the result of a function
 193   that returns a composite value, you'd need to write something like:
 194
 195 <programlisting>
 196 SELECT (my_func(...)).field FROM ...
 197 </programlisting>
 198
 199   Without the extra parentheses, this will generate a syntax error.
 200  </para>
 201  </sect2>
 202
 203  <sect2>
 204   <title>Modifying Composite Types</title>
 205
 206  <para>
 207   Here are some examples of the proper syntax for inserting and updating
 208   composite columns.
 209   First, inserting or updating a whole column:
 210
 211 <programlisting>
 212 INSERT INTO mytab (complex_col) VALUES((1.1,2.2));
 213
 214 UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;
 215 </programlisting>
 216
 217   The first example omits <literal>ROW</>, the second uses it; we
 218   could have done it either way.
 219  </para>
 220
 221  <para>
 222   We can update an individual subfield of a composite column:
 223
 224 <programlisting>
 225 UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;
 226 </programlisting>
 227
 228   Notice here that we don't need to (and indeed cannot)
 229   put parentheses around the column name appearing just after
 230   <literal>SET</>, but we do need parentheses when referencing the same
 231   column in the expression to the right of the equal sign.
 232  </para>
 233
 234  <para>
 235   And we can specify subfields as targets for <command>INSERT</>, too:
 236
 237 <programlisting>
 238 INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);
 239 </programlisting>
 240
 241   Had we not supplied values for all the subfields of the column, the
 242   remaining subfields would have been filled with null values.
 243  </para>
 244  </sect2>
 245
 246  <sect2>
 247   <title>Composite Type Input and Output Syntax</title>
 248
 249   <para>
 250    The external text representation of a composite value consists of items that
 251    are interpreted according to the I/O conversion rules for the individual
 252    field types, plus decoration that indicates the composite structure.
 253    The decoration consists of parentheses (<literal>(</> and <literal>)</>)
 254    around the whole value, plus commas (<literal>,</>) between adjacent
 255    items.  Whitespace outside the parentheses is ignored, but within the
 256    parentheses it is considered part of the field value, and might or might not be
 257    significant depending on the input conversion rules for the field data type.
 258    For example, in:
 259 <programlisting>
 260 '(  42)'
 261 </programlisting>
 262    the whitespace will be ignored if the field type is integer, but not if
 263    it is text.
 264   </para>
 265
 266   <para>
 267    As shown previously, when writing a composite value you can write double
 268    quotes around any individual field value.
 269    You <emphasis>must</> do so if the field value would otherwise
 270    confuse the composite-value parser.  In particular, fields containing
 271    parentheses, commas, double quotes, or backslashes must be double-quoted.
 272    To put a double quote or backslash in a quoted composite field value,
 273    precede it with a backslash.  (Also, a pair of double quotes within a
 274    double-quoted field value is taken to represent a double quote character,
 275    analogously to the rules for single quotes in SQL literal strings.)
 276    Alternatively, you can avoid quoting and use backslash-escaping to
 277    protect all data characters
 278    that would otherwise be taken as composite syntax.
 279   </para>
 280
 281   <para>
 282    A completely empty field value (no characters at all between the commas
 283    or parentheses) represents a NULL.  To write a value that is an empty
 284    string rather than NULL, write <literal>""</>.
 285   </para>
 286
 287   <para>
 288    The composite output routine will put double quotes around field values
 289    if they are empty strings or contain parentheses, commas,
 290    double quotes, backslashes, or white space.  (Doing so for white space
 291    is not essential, but aids legibility.)  Double quotes and backslashes
 292    embedded in field values will be doubled.
 293   </para>
 294
 295  <note>
 296   <para>
 297    Remember that what you write in an SQL command will first be interpreted
 298    as a string literal, and then as a composite.  This doubles the number of
 299    backslashes you need (assuming escape string syntax is used).
 300    For example, to insert a <type>text</> field
 301    containing a double quote and a backslash in a composite
 302    value, you'd need to write:
 303 <programlisting>
 304 INSERT ... VALUES (E'("\\"\\\\")');
 305 </programlisting>
 306    The string-literal processor removes one level of backslashes, so that
 307    what arrives at the composite-value parser looks like
 308    <literal>("\"\\")</>.  In turn, the string
 309    fed to the <type>text</> data type's input routine
 310    becomes <literal>"\</>.  (If we were working
 311    with a data type whose input routine also treated backslashes specially,
 312    <type>bytea</> for example, we might need as many as eight backslashes
 313    in the command to get one backslash into the stored composite field.)
 314    Dollar quoting (see <xref linkend="sql-syntax-dollar-quoting">) can be
 315    used to avoid the need to double backslashes.
 316   </para>
 317  </note>
 318
 319  <tip>
 320   <para>
 321    The <literal>ROW</> constructor syntax is usually easier to work with
 322    than the composite-literal syntax when writing composite values in SQL
 323    commands.
 324    In <literal>ROW</>, individual field values are written the same way
 325    they would be written when not members of a composite.
 326   </para>
 327  </tip>
 328  </sect2>
 329
 330 </sect1>