diff options
Diffstat (limited to 'doc/src/sgml/ref/create_cast.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_cast.sgml | 424 |
1 files changed, 424 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/create_cast.sgml b/doc/src/sgml/ref/create_cast.sgml new file mode 100644 index 0000000..bad75bc --- /dev/null +++ b/doc/src/sgml/ref/create_cast.sgml @@ -0,0 +1,424 @@ +<!-- +doc/src/sgml/ref/create_cast.sgml +PostgreSQL documentation +--> + +<refentry id="sql-createcast"> + <indexterm zone="sql-createcast"> + <primary>CREATE CAST</primary> + </indexterm> + + <refmeta> + <refentrytitle>CREATE CAST</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>CREATE CAST</refname> + <refpurpose>define a new cast</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +CREATE CAST (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>) + WITH FUNCTION <replaceable>function_name</replaceable> [ (<replaceable>argument_type</replaceable> [, ...]) ] + [ AS ASSIGNMENT | AS IMPLICIT ] + +CREATE CAST (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>) + WITHOUT FUNCTION + [ AS ASSIGNMENT | AS IMPLICIT ] + +CREATE CAST (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>) + WITH INOUT + [ AS ASSIGNMENT | AS IMPLICIT ] +</synopsis> + </refsynopsisdiv> + + <refsect1 id="sql-createcast-description"> + <title>Description</title> + + <para> + <command>CREATE CAST</command> defines a new cast. A cast + specifies how to perform a conversion between + two data types. For example, +<programlisting> +SELECT CAST(42 AS float8); +</programlisting> + converts the integer constant 42 to type <type>float8</type> by + invoking a previously specified function, in this case + <literal>float8(int4)</literal>. (If no suitable cast has been defined, the + conversion fails.) + </para> + + <para> + Two types can be <firstterm>binary coercible</firstterm>, which + means that the conversion can be performed <quote>for free</quote> + without invoking any function. This requires that corresponding + values use the same internal representation. For instance, the + types <type>text</type> and <type>varchar</type> are binary + coercible both ways. Binary coercibility is not necessarily a + symmetric relationship. For example, the cast + from <type>xml</type> to <type>text</type> can be performed for + free in the present implementation, but the reverse direction + requires a function that performs at least a syntax check. (Two + types that are binary coercible both ways are also referred to as + binary compatible.) + </para> + + <para> + You can define a cast as an <firstterm>I/O conversion cast</firstterm> by using + the <literal>WITH INOUT</literal> syntax. An I/O conversion cast is + performed by invoking the output function of the source data type, and + passing the resulting string to the input function of the target data type. + In many common cases, this feature avoids the need to write a separate + cast function for conversion. An I/O conversion cast acts the same as + a regular function-based cast; only the implementation is different. + </para> + + <para> + By default, a cast can be invoked only by an explicit cast request, + that is an explicit <literal>CAST(<replaceable>x</replaceable> AS + <replaceable>typename</replaceable>)</literal> or + <replaceable>x</replaceable><literal>::</literal><replaceable>typename</replaceable> + construct. + </para> + + <para> + If the cast is marked <literal>AS ASSIGNMENT</literal> then it can be invoked + implicitly when assigning a value to a column of the target data type. + For example, supposing that <literal>foo.f1</literal> is a column of + type <type>text</type>, then: +<programlisting> +INSERT INTO foo (f1) VALUES (42); +</programlisting> + will be allowed if the cast from type <type>integer</type> to type + <type>text</type> is marked <literal>AS ASSIGNMENT</literal>, otherwise not. + (We generally use the term <firstterm>assignment + cast</firstterm> to describe this kind of cast.) + </para> + + <para> + If the cast is marked <literal>AS IMPLICIT</literal> then it can be invoked + implicitly in any context, whether assignment or internally in an + expression. (We generally use the term <firstterm>implicit + cast</firstterm> to describe this kind of cast.) + For example, consider this query: +<programlisting> +SELECT 2 + 4.0; +</programlisting> + The parser initially marks the constants as being of type <type>integer</type> + and <type>numeric</type> respectively. There is no <type>integer</type> + <literal>+</literal> <type>numeric</type> operator in the system catalogs, + but there is a <type>numeric</type> <literal>+</literal> <type>numeric</type> operator. + The query will therefore succeed if a cast from <type>integer</type> to + <type>numeric</type> is available and is marked <literal>AS IMPLICIT</literal> — + which in fact it is. The parser will apply the implicit cast and resolve + the query as if it had been written +<programlisting> +SELECT CAST ( 2 AS numeric ) + 4.0; +</programlisting> + </para> + + <para> + Now, the catalogs also provide a cast from <type>numeric</type> to + <type>integer</type>. If that cast were marked <literal>AS IMPLICIT</literal> — + which it is not — then the parser would be faced with choosing + between the above interpretation and the alternative of casting the + <type>numeric</type> constant to <type>integer</type> and applying the + <type>integer</type> <literal>+</literal> <type>integer</type> operator. Lacking any + knowledge of which choice to prefer, it would give up and declare the + query ambiguous. The fact that only one of the two casts is + implicit is the way in which we teach the parser to prefer resolution + of a mixed <type>numeric</type>-and-<type>integer</type> expression as + <type>numeric</type>; there is no built-in knowledge about that. + </para> + + <para> + It is wise to be conservative about marking casts as implicit. An + overabundance of implicit casting paths can cause + <productname>PostgreSQL</productname> to choose surprising + interpretations of commands, or to be unable to resolve commands at + all because there are multiple possible interpretations. A good + rule of thumb is to make a cast implicitly invokable only for + information-preserving transformations between types in the same + general type category. For example, the cast from <type>int2</type> to + <type>int4</type> can reasonably be implicit, but the cast from + <type>float8</type> to <type>int4</type> should probably be + assignment-only. Cross-type-category casts, such as <type>text</type> + to <type>int4</type>, are best made explicit-only. + </para> + + <note> + <para> + Sometimes it is necessary for usability or standards-compliance reasons + to provide multiple implicit casts among a set of types, resulting in + ambiguity that cannot be avoided as above. The parser has a fallback + heuristic based on <firstterm>type categories</firstterm> and <firstterm>preferred + types</firstterm> that can help to provide desired behavior in such cases. See + <xref linkend="sql-createtype"/> for + more information. + </para> + </note> + + <para> + To be able to create a cast, you must own the source or the target data type + and have <literal>USAGE</literal> privilege on the other type. To create a + binary-coercible cast, you must be superuser. (This restriction is made + because an erroneous binary-coercible cast conversion can easily crash the + server.) + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable>source_type</replaceable></term> + + <listitem> + <para> + The name of the source data type of the cast. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable>target_type</replaceable></term> + + <listitem> + <para> + The name of the target data type of the cast. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal><replaceable>function_name</replaceable>[(<replaceable>argument_type</replaceable> [, ...])]</literal></term> + + <listitem> + <para> + The function used to perform the cast. The function name can + be schema-qualified. If it is not, the function will be looked + up in the schema search path. The function's result data type must + match the target type of the cast. Its arguments are discussed below. + If no argument list is specified, the function name must be unique in + its schema. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>WITHOUT FUNCTION</literal></term> + + <listitem> + <para> + Indicates that the source type is binary-coercible to the target type, + so no function is required to perform the cast. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>WITH INOUT</literal></term> + + <listitem> + <para> + Indicates that the cast is an I/O conversion cast, performed by + invoking the output function of the source data type, and passing the + resulting string to the input function of the target data type. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>AS ASSIGNMENT</literal></term> + + <listitem> + <para> + Indicates that the cast can be invoked implicitly in assignment + contexts. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>AS IMPLICIT</literal></term> + + <listitem> + <para> + Indicates that the cast can be invoked implicitly in any context. + </para> + </listitem> + </varlistentry> + </variablelist> + + <para> + Cast implementation functions can have one to three arguments. + The first argument type must be identical to or binary-coercible from + the cast's source type. The second argument, + if present, must be type <type>integer</type>; it receives the type + modifier associated with the destination type, or <literal>-1</literal> + if there is none. The third argument, + if present, must be type <type>boolean</type>; it receives <literal>true</literal> + if the cast is an explicit cast, <literal>false</literal> otherwise. + (Bizarrely, the SQL standard demands different behaviors for explicit and + implicit casts in some cases. This argument is supplied for functions + that must implement such casts. It is not recommended that you design + your own data types so that this matters.) + </para> + + <para> + The return type of a cast function must be identical to or + binary-coercible to the cast's target type. + </para> + + <para> + Ordinarily a cast must have different source and target data types. + However, it is allowed to declare a cast with identical source and + target types if it has a cast implementation function with more than one + argument. This is used to represent type-specific length coercion + functions in the system catalogs. The named function is used to + coerce a value of the type to the type modifier value given by its + second argument. + </para> + + <para> + When a cast has different source and + target types and a function that takes more than one argument, it + supports converting from one type to another and applying a length + coercion in a single step. When no such entry is available, coercion + to a type that uses a type modifier involves two cast steps, one to + convert between data types and a second to apply the modifier. + </para> + + <para> + A cast to or from a domain type currently has no effect. Casting + to or from a domain uses the casts associated with its underlying type. + </para> + + </refsect1> + + <refsect1 id="sql-createcast-notes"> + <title>Notes</title> + + <para> + Use <link linkend="sql-dropcast"><command>DROP CAST</command></link> to remove user-defined casts. + </para> + + <para> + Remember that if you want to be able to convert types both ways you + need to declare casts both ways explicitly. + </para> + + <indexterm zone="sql-createcast"> + <primary>cast</primary> + <secondary>I/O conversion</secondary> + </indexterm> + + <para> + It is normally not necessary to create casts between user-defined types + and the standard string types (<type>text</type>, <type>varchar</type>, and + <type>char(<replaceable>n</replaceable>)</type>, as well as user-defined types that + are defined to be in the string category). <productname>PostgreSQL</productname> + provides automatic I/O conversion casts for that. The automatic casts to + string types are treated as assignment casts, while the automatic casts + from string types are + explicit-only. You can override this behavior by declaring your own + cast to replace an automatic cast, but usually the only reason to + do so is if you want the conversion to be more easily invokable than the + standard assignment-only or explicit-only setting. Another possible + reason is that you want the conversion to behave differently from the + type's I/O function; but that is sufficiently surprising that you + should think twice about whether it's a good idea. (A small number of + the built-in types do indeed have different behaviors for conversions, + mostly because of requirements of the SQL standard.) + </para> + + <para> + While not required, it is recommended that you continue to follow this old + convention of naming cast implementation functions after the target data + type. Many users are used to being able to cast data types using a + function-style notation, that is + <replaceable>typename</replaceable>(<replaceable>x</replaceable>). This notation is in fact + nothing more nor less than a call of the cast implementation function; it + is not specially treated as a cast. If your conversion functions are not + named to support this convention then you will have surprised users. + Since <productname>PostgreSQL</productname> allows overloading of the same function + name with different argument types, there is no difficulty in having + multiple conversion functions from different types that all use the + target type's name. + </para> + + <note> + <para> + Actually the preceding paragraph is an oversimplification: there are + two cases in which a function-call construct will be treated as a cast + request without having matched it to an actual function. + If a function call <replaceable>name</replaceable>(<replaceable>x</replaceable>) does not + exactly match any existing function, but <replaceable>name</replaceable> is the name + of a data type and <structname>pg_cast</structname> provides a binary-coercible cast + to this type from the type of <replaceable>x</replaceable>, then the call will be + construed as a binary-coercible cast. This exception is made so that + binary-coercible casts can be invoked using functional syntax, even + though they lack any function. Likewise, if there is no + <structname>pg_cast</structname> entry but the cast would be to or from a string + type, the call will be construed as an I/O conversion cast. This + exception allows I/O conversion casts to be invoked using functional + syntax. + </para> + </note> + + <note> + <para> + There is also an exception to the exception: I/O conversion casts from + composite types to string types cannot be invoked using functional + syntax, but must be written in explicit cast syntax (either + <literal>CAST</literal> or <literal>::</literal> notation). This exception was added + because after the introduction of automatically-provided I/O conversion + casts, it was found too easy to accidentally invoke such a cast when + a function or column reference was intended. + </para> + </note> + </refsect1> + + + <refsect1 id="sql-createcast-examples"> + <title>Examples</title> + + <para> + To create an assignment cast from type <type>bigint</type> to type + <type>int4</type> using the function <literal>int4(bigint)</literal>: +<programlisting> +CREATE CAST (bigint AS int4) WITH FUNCTION int4(bigint) AS ASSIGNMENT; +</programlisting> + (This cast is already predefined in the system.) + </para> + </refsect1> + + <refsect1 id="sql-createcast-compat"> + <title>Compatibility</title> + + <para> + The <command>CREATE CAST</command> command conforms to the + <acronym>SQL</acronym> standard, + except that SQL does not make provisions for binary-coercible + types or extra arguments to implementation functions. + <literal>AS IMPLICIT</literal> is a <productname>PostgreSQL</productname> + extension, too. + </para> + </refsect1> + + + <refsect1 id="sql-createcast-seealso"> + <title>See Also</title> + + <para> + <xref linkend="sql-createfunction"/>, + <xref linkend="sql-createtype"/>, + <xref linkend="sql-dropcast"/> + </para> + </refsect1> + +</refentry> |