doc/src/sgml/ref/create_cast.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <refentry id="SQL-CREATECAST">
   4  <refmeta>
   5   <refentrytitle id="SQL-CREATECAST-TITLE">CREATE CAST</refentrytitle>
   6   <manvolnum>7</manvolnum>
   7   <refmiscinfo>SQL - Language Statements</refmiscinfo>
   8  </refmeta>
   9
  10  <refnamediv>
  11   <refname>CREATE CAST</refname>
  12   <refpurpose>define a new cast</refpurpose>
  13  </refnamediv>
  14
  15  <indexterm zone="sql-createcast">
  16   <primary>CREATE CAST</primary>
  17  </indexterm>
  18
  19  <refsynopsisdiv>
  20 <synopsis>
  21 CREATE CAST (<replaceable>sourcetype</replaceable> AS <replaceable>targettype</replaceable>)
  22     WITH FUNCTION <replaceable>funcname</replaceable> (<replaceable>argtypes</replaceable>)
  23     [ AS ASSIGNMENT | AS IMPLICIT ]
  24
  25 CREATE CAST (<replaceable>sourcetype</replaceable> AS <replaceable>targettype</replaceable>)
  26     WITHOUT FUNCTION
  27     [ AS ASSIGNMENT | AS IMPLICIT ]
  28
  29 CREATE CAST (<replaceable>sourcetype</replaceable> AS <replaceable>targettype</replaceable>)
  30     WITH INOUT
  31     [ AS ASSIGNMENT | AS IMPLICIT ]
  32 </synopsis>
  33  </refsynopsisdiv>
  34
  35  <refsect1 id="sql-createcast-description">
  36   <title>Description</title>
  37
  38   <para>
  39    <command>CREATE CAST</command> defines a new cast.  A cast
  40    specifies how to perform a conversion between
  41    two data types.  For example:
  42 <programlisting>
  43 SELECT CAST(42 AS float8);
  44 </programlisting>
  45    converts the integer constant 42 to type <type>float8</type> by
  46    invoking a previously specified function, in this case
  47    <literal>float8(int4)</>. (If no suitable cast has been defined, the
  48    conversion fails.)
  49   </para>
  50
  51   <para>
  52    Two types can be <firstterm>binary coercible</firstterm>, which
  53    means that the conversion can be performed <quote>for free</quote>
  54    without invoking any function.  This requires that corresponding
  55    values use the same internal representation.  For instance, the
  56    types <type>text</type> and <type>varchar</type> are binary
  57    coercible both ways.  Binary coercibility is not necessarily a
  58    symmetric relationship.  For example, the cast
  59    from <type>xml</type> to <type>text</type> can be performed for
  60    free in the present implementation, but the reverse direction
  61    requires a function that performs at least a syntax check.  (Two
  62    types that are binary coercible both ways are also referred to as
  63    binary compatible.)
  64   </para>
  65
  66   <para>
  67    You can define a cast as an <firstterm>I/O conversion cast</> using
  68    the <literal>WITH INOUT</literal> syntax. An I/O conversion cast is
  69    performed by invoking the output function of the source data type, and
  70    passing the result to the input function of the target data type.
  71   </para>
  72
  73   <para>
  74    By default, a cast can be invoked only by an explicit cast request,
  75    that is an explicit <literal>CAST(<replaceable>x</> AS
  76    <replaceable>typename</>)</literal> or
  77    <replaceable>x</><literal>::</><replaceable>typename</>
  78    construct.
  79   </para>
  80
  81   <para>
  82    If the cast is marked <literal>AS ASSIGNMENT</> then it can be invoked
  83    implicitly when assigning a value to a column of the target data type.
  84    For example, supposing that <literal>foo.f1</literal> is a column of
  85    type <type>text</type>, then:
  86 <programlisting>
  87 INSERT INTO foo (f1) VALUES (42);
  88 </programlisting>
  89    will be allowed if the cast from type <type>integer</type> to type
  90    <type>text</type> is marked <literal>AS ASSIGNMENT</>, otherwise not.
  91    (We generally use the term <firstterm>assignment
  92    cast</firstterm> to describe this kind of cast.)
  93   </para>
  94
  95   <para>
  96    If the cast is marked <literal>AS IMPLICIT</> then it can be invoked
  97    implicitly in any context, whether assignment or internally in an
  98    expression.  (We generally use the term <firstterm>implicit
  99    cast</firstterm> to describe this kind of cast.)
 100    For example, consider this query:
 101 <programlisting>
 102 SELECT 2 + 4.0;
 103 </programlisting>
 104    The parser initially marks the constants as being of type <type>integer</>
 105    and <type>numeric</> respectively.  There is no <type>integer</>
 106    <literal>+</> <type>numeric</> operator in the system catalogs,
 107    but there is a <type>numeric</> <literal>+</> <type>numeric</> operator.
 108    The query will therefore succeed if a cast from <type>integer</> to
 109    <type>numeric</> is available and is marked <literal>AS IMPLICIT</> &mdash;
 110    which in fact it is.  The parser will apply the implicit cast and resolve
 111    the query as if it had been written
 112 <programlisting>
 113 SELECT CAST ( 2 AS numeric ) + 4.0;
 114 </programlisting>
 115   </para>
 116
 117   <para>
 118    Now, the catalogs also provide a cast from <type>numeric</> to
 119    <type>integer</>.  If that cast were marked <literal>AS IMPLICIT</> &mdash;
 120    which it is not &mdash; then the parser would be faced with choosing
 121    between the above interpretation and the alternative of casting the
 122    <type>numeric</> constant to <type>integer</> and applying the
 123    <type>integer</> <literal>+</> <type>integer</> operator.  Lacking any
 124    knowledge of which choice to prefer, it would give up and declare the
 125    query ambiguous.  The fact that only one of the two casts is
 126    implicit is the way in which we teach the parser to prefer resolution
 127    of a mixed <type>numeric</>-and-<type>integer</> expression as
 128    <type>numeric</>; there is no built-in knowledge about that.
 129   </para>
 130
 131   <para>
 132    It is wise to be conservative about marking casts as implicit.  An
 133    overabundance of implicit casting paths can cause
 134    <productname>PostgreSQL</productname> to choose surprising
 135    interpretations of commands, or to be unable to resolve commands at
 136    all because there are multiple possible interpretations.  A good
 137    rule of thumb is to make a cast implicitly invokable only for
 138    information-preserving transformations between types in the same
 139    general type category.  For example, the cast from <type>int2</type> to
 140    <type>int4</type> can reasonably be implicit, but the cast from
 141    <type>float8</type> to <type>int4</type> should probably be
 142    assignment-only.  Cross-type-category casts, such as <type>text</>
 143    to <type>int4</>, are best made explicit-only.
 144   </para>
 145
 146   <note>
 147    <para>
 148     Sometimes it is necessary for usability or standards-compliance reasons
 149     to provide multiple implicit casts among a set of types, resulting in
 150     ambiguity that cannot be avoided as above.  The parser has a fallback
 151     heuristic based on <firstterm>type categories</> and <firstterm>preferred
 152     types</> that can help to provide desired behavior in such cases.  See
 153     <xref linkend="sql-createtype" endterm="sql-createtype-title"> for
 154     more information.
 155    </para>
 156   </note>
 157
 158   <para>
 159    To be able to create a cast, you must own the source or the target
 160    data type.  To create a binary-coercible cast, you must be superuser.
 161    (This restriction is made because an erroneous binary-coercible cast
 162    conversion can easily crash the server.)
 163   </para>
 164  </refsect1>
 165
 166  <refsect1>
 167   <title>Parameters</title>
 168
 169    <variablelist>
 170     <varlistentry>
 171      <term><replaceable>sourcetype</replaceable></term>
 172
 173      <listitem>
 174       <para>
 175        The name of the source data type of the cast.
 176       </para>
 177      </listitem>
 178     </varlistentry>
 179
 180     <varlistentry>
 181      <term><replaceable>targettype</replaceable></term>
 182
 183      <listitem>
 184       <para>
 185        The name of the target data type of the cast.
 186       </para>
 187      </listitem>
 188     </varlistentry>
 189
 190     <varlistentry>
 191      <term><replaceable>funcname</replaceable>(<replaceable>argtypes</replaceable>)</term>
 192
 193      <listitem>
 194       <para>
 195        The function used to perform the cast.  The function name can
 196        be schema-qualified.  If it is not, the function will be looked
 197        up in the schema search path.  The function's result data type must
 198        match the target type of the cast.   Its arguments are discussed below.
 199       </para>
 200      </listitem>
 201     </varlistentry>
 202
 203     <varlistentry>
 204      <term><literal>WITHOUT FUNCTION</literal></term>
 205
 206      <listitem>
 207       <para>
 208        Indicates that the source type is binary-coercible to the target type,
 209        so no function is required to perform the cast.
 210       </para>
 211      </listitem>
 212     </varlistentry>
 213
 214     <varlistentry>
 215      <term><literal>WITH INOUT</literal></term>
 216
 217      <listitem>
 218       <para>
 219        Indicates that the cast is an I/O conversion cast, performed by
 220        invoking the output function of the source data type, and passing the
 221        result to the input function of the target data type.
 222       </para>
 223      </listitem>
 224     </varlistentry>
 225
 226     <varlistentry>
 227      <term><literal>AS ASSIGNMENT</literal></term>
 228
 229      <listitem>
 230       <para>
 231        Indicates that the cast can be invoked implicitly in assignment
 232        contexts.
 233       </para>
 234      </listitem>
 235     </varlistentry>
 236
 237     <varlistentry>
 238      <term><literal>AS IMPLICIT</literal></term>
 239
 240      <listitem>
 241       <para>
 242        Indicates that the cast can be invoked implicitly in any context.
 243       </para>
 244      </listitem>
 245     </varlistentry>
 246    </variablelist>
 247
 248   <para>
 249    Cast implementation functions can have one to three arguments.
 250    The first argument type must be identical to or binary-coercible from
 251    the cast's source type.  The second argument,
 252    if present, must be type <type>integer</>; it receives the type
 253    modifier associated with the destination type, or <literal>-1</>
 254    if there is none.  The third argument,
 255    if present, must be type <type>boolean</>; it receives <literal>true</>
 256    if the cast is an explicit cast, <literal>false</> otherwise.
 257    (Bizarrely, the SQL standard demands different behaviors for explicit and
 258    implicit casts in some cases.  This argument is supplied for functions
 259    that must implement such casts.  It is not recommended that you design
 260    your own data types so that this matters.)
 261   </para>
 262
 263   <para>
 264    The return type of a cast function must be identical to or
 265    binary-coercible to the cast's target type.
 266   </para>
 267
 268   <para>
 269    Ordinarily a cast must have different source and target data types.
 270    However, it is allowed to declare a cast with identical source and
 271    target types if it has a cast implementation function with more than one
 272    argument.  This is used to represent type-specific length coercion
 273    functions in the system catalogs.  The named function is used to
 274    coerce a value of the type to the type modifier value given by its
 275    second argument.
 276   </para>
 277
 278   <para>
 279    When a cast has different source and
 280    target types and a function that takes more than one argument, it
 281    represents converting from one type to another and applying a length
 282    coercion in a single step.  When no such entry is available, coercion
 283    to a type that uses a type modifier involves two steps, one to
 284    convert between data types and a second to apply the modifier.
 285   </para>
 286
 287  </refsect1>
 288
 289  <refsect1 id="sql-createcast-notes">
 290   <title>Notes</title>
 291
 292   <para>
 293    Use <xref linkend="sql-dropcast"
 294    endterm="sql-dropcast-title"> to remove user-defined casts.
 295   </para>
 296
 297   <para>
 298    Remember that if you want to be able to convert types both ways you
 299    need to declare casts both ways explicitly.
 300   </para>
 301
 302  <indexterm zone="sql-createcast">
 303   <primary>cast</primary>
 304   <secondary>I/O conversion</secondary>
 305  </indexterm>
 306
 307   <para>
 308    It is normally not necessary to create casts between user-defined types
 309    and the standard string types (<type>text</>, <type>varchar</>, and
 310    <type>char(<replaceable>n</>)</type>, as well as user-defined types that
 311    are defined to be in the string category).  <productname>PostgreSQL</>
 312    provides automatic I/O conversion casts for that. The automatic casts to
 313    string types are treated as assignment casts, while the automatic casts
 314    from string types are
 315    explicit-only.  You can override this behavior by declaring your own
 316    cast to replace an automatic cast, but usually the only reason to
 317    do so is if you want the conversion to be more easily invokable than the
 318    standard assignment-only or explicit-only setting.  Another possible
 319    reason is that you want the conversion to behave differently from the
 320    type's I/O function; but that is sufficiently surprising that you
 321    should think twice about whether it's a good idea.  (A small number of
 322    the built-in types do indeed have different behaviors for conversions,
 323    mostly because of requirements of the SQL standard.)
 324   </para>
 325
 326   <para>
 327    Prior to <productname>PostgreSQL</> 7.3, every function that had
 328    the same name as a data type, returned that data type, and took one
 329    argument of a different type was automatically a cast function.
 330    This convention has been abandoned in face of the introduction of
 331    schemas and to be able to represent binary-coercible casts in the
 332    system catalogs.  The built-in cast functions still follow this naming
 333    scheme, but they have to be shown as casts in the system catalog
 334    <structname>pg_cast</> as well.
 335   </para>
 336
 337   <para>
 338    While not required, it is recommended that you continue to follow this old
 339    convention of naming cast implementation functions after the target data
 340    type.  Many users are used to being able to cast data types using a
 341    function-style notation, that is
 342    <replaceable>typename</>(<replaceable>x</>).  This notation is in fact
 343    nothing more nor less than a call of the cast implementation function; it
 344    is not specially treated as a cast.  If your conversion functions are not
 345    named to support this convention then you will have surprised users.
 346    Since <productname>PostgreSQL</> allows overloading of the same function
 347    name with different argument types, there is no difficulty in having
 348    multiple conversion functions from different types that all use the
 349    target type's name.
 350   </para>
 351
 352   <note>
 353    <para>
 354     Actually the preceding paragraph is an oversimplification: there are
 355     two cases in which a function-call construct will be treated as a cast
 356     request without having matched it to an actual function.
 357     If a function call <replaceable>name</>(<replaceable>x</>) does not
 358     exactly match any existing function, but <replaceable>name</> is the name
 359     of a data type and <structname>pg_cast</> provides a binary-coercible cast
 360     to this type from the type of <replaceable>x</>, then the call will be
 361     construed as a binary-coercible cast.  This exception is made so that
 362     binary-coercible casts can be invoked using functional syntax, even
 363     though they lack any function.  Likewise, if there is no
 364     <structname>pg_cast</> entry but the cast would be to or from a string
 365     type, the call will be construed as an I/O conversion cast.  This
 366     exception allows I/O conversion casts to be invoked using functional
 367     syntax.
 368    </para>
 369   </note>
 370  </refsect1>
 371
 372
 373  <refsect1 id="sql-createcast-examples">
 374   <title>Examples</title>
 375
 376   <para>
 377    To create an assignment cast from type <type>bigint</type> to type
 378    <type>int4</type> using the function <literal>int4(bigint)</literal>:
 379 <programlisting>
 380 CREATE CAST (bigint AS int4) WITH FUNCTION int4(bigint) AS ASSIGNMENT;
 381 </programlisting>
 382    (This cast is already predefined in the system.)
 383   </para>
 384  </refsect1>
 385
 386  <refsect1 id="sql-createcast-compat">
 387   <title>Compatibility</title>
 388
 389   <para>
 390    The <command>CREATE CAST</command> command conforms to the
 391    <acronym>SQL</acronym> standard,
 392    except that SQL does not make provisions for binary-coercible
 393    types or extra arguments to implementation functions.
 394    <literal>AS IMPLICIT</> is a <productname>PostgreSQL</productname>
 395    extension, too.
 396   </para>
 397  </refsect1>
 398
 399
 400  <refsect1 id="sql-createcast-seealso">
 401   <title>See Also</title>
 402
 403   <para>
 404    <xref linkend="sql-createfunction" endterm="sql-createfunction-title">,
 405    <xref linkend="sql-createtype" endterm="sql-createtype-title">,
 406    <xref linkend="sql-dropcast" endterm="sql-dropcast-title">
 407   </para>
 408  </refsect1>
 409
 410 </refentry>