| <!-- $PostgreSQL: pgsql/doc/src/sgml/ref/create_cast.sgml,v 1.23 2006/09/16 00:30:17 momjian Exp $ --> |
| |
| <refentry id="SQL-CREATECAST"> |
| <refmeta> |
| <refentrytitle id="SQL-CREATECAST-TITLE">CREATE CAST</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>CREATE CAST</refname> |
| <refpurpose>define a new cast</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-createcast"> |
| <primary>CREATE CAST</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| CREATE CAST (<replaceable>sourcetype</replaceable> AS <replaceable>targettype</replaceable>) |
| WITH FUNCTION <replaceable>funcname</replaceable> (<replaceable>argtypes</replaceable>) |
| [ AS ASSIGNMENT | AS IMPLICIT ] |
| |
| CREATE CAST (<replaceable>sourcetype</replaceable> AS <replaceable>targettype</replaceable>) |
| WITHOUT FUNCTION |
| [ 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 text); |
| </programlisting> |
| converts the integer constant 42 to type <type>text</type> by |
| invoking a previously specified function, in this case |
| <literal>text(int4)</>. (If no suitable cast has been defined, the |
| conversion fails.) |
| </para> |
| |
| <para> |
| Two types may be <firstterm>binary compatible</firstterm>, which |
| means that they can be converted into one another <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 compatible. |
| </para> |
| |
| <para> |
| By default, a cast can be invoked only by an explicit cast request, |
| that is an explicit <literal>CAST(<replaceable>x</> AS |
| <replaceable>typename</>)</literal> or |
| <replaceable>x</><literal>::</><replaceable>typename</> |
| construct. |
| </para> |
| |
| <para> |
| If the cast is marked <literal>AS ASSIGNMENT</> 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</>, 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</> then it can be invoked |
| implicitly in any context, whether assignment or internally in an |
| expression. For example, since <literal>||</> takes <type>text</> |
| operands, |
| <programlisting> |
| SELECT 'The time is ' || now(); |
| </programlisting> |
| will be allowed only if the cast from type <type>timestamp</> to |
| <type>text</type> is marked <literal>AS IMPLICIT</>. Otherwise it |
| will be necessary to write the cast explicitly, for example |
| <programlisting> |
| SELECT 'The time is ' || CAST(now() AS text); |
| </programlisting> |
| (We generally use the term <firstterm>implicit |
| cast</firstterm> to describe this kind of cast.) |
| </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</> |
| to <type>int4</>, are best made explicit-only. |
| </para> |
| |
| <para> |
| To be able to create a cast, you must own the source or the target |
| data type. To create a binary-compatible cast, you must be superuser. |
| (This restriction is made because an erroneous binary-compatible cast |
| conversion can easily crash the server.) |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><replaceable>sourcetype</replaceable></term> |
| |
| <listitem> |
| <para> |
| The name of the source data type of the cast. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable>targettype</replaceable></term> |
| |
| <listitem> |
| <para> |
| The name of the target data type of the cast. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable>funcname</replaceable>(<replaceable>argtypes</replaceable>)</term> |
| |
| <listitem> |
| <para> |
| The function used to perform the cast. The function name may |
| 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. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>WITHOUT FUNCTION</literal></term> |
| |
| <listitem> |
| <para> |
| Indicates that the source type and the target type are binary |
| compatible, so no function is required to perform the cast. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>AS ASSIGNMENT</literal></term> |
| |
| <listitem> |
| <para> |
| Indicates that the cast may be invoked implicitly in assignment |
| contexts. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>AS IMPLICIT</literal></term> |
| |
| <listitem> |
| <para> |
| Indicates that the cast may be invoked implicitly in any context. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| |
| <para> |
| Cast implementation functions may have one to three arguments. |
| The first argument type must be identical to the cast's source type. |
| The second argument, |
| if present, must be type <type>integer</>; it receives the type |
| modifier associated with the destination type, or <literal>-1</> |
| if there is none. The third argument, |
| if present, must be type <type>boolean</>; it receives <literal>true</> |
| if the cast is an explicit cast, <literal>false</> otherwise. |
| (Bizarrely, the SQL spec 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> |
| 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. (Since the grammar presently permits only certain |
| built-in data types to have type modifiers, this feature is of no |
| use for user-defined target types, but we mention it for completeness.) |
| </para> |
| |
| <para> |
| When a cast has different source and |
| target types and a function that takes more than one argument, it |
| represents 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 steps, one to |
| convert between data types and a second to apply the modifier. |
| </para> |
| |
| </refsect1> |
| |
| <refsect1 id="sql-createcast-notes"> |
| <title>Notes</title> |
| |
| <para> |
| Use <xref linkend="sql-dropcast" |
| endterm="sql-dropcast-title"> 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> |
| |
| <para> |
| Prior to <productname>PostgreSQL</> 7.3, every function that had |
| the same name as a data type, returned that data type, and took one |
| argument of a different type was automatically a cast function. |
| This convention has been abandoned in face of the introduction of |
| schemas and to be able to represent binary compatible casts in the |
| system catalogs. The built-in cast functions still follow this naming |
| scheme, but they have to be shown as casts in the system catalog |
| <structname>pg_cast</> as well. |
| </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>x</>). 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</> 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> |
| There is one small lie in the preceding paragraph: there is still one |
| case in which <structname>pg_cast</> will be used to resolve the |
| meaning of an apparent function call. If a |
| function call <replaceable>name</>(<replaceable>x</>) matches no |
| actual function, but <replaceable>name</> is the name of a data type |
| and <structname>pg_cast</> shows a binary-compatible cast to this |
| type from the type of <replaceable>x</>, then the call will be construed |
| as an explicit cast. This exception is made so that binary-compatible |
| casts can be invoked using functional syntax, even though they lack |
| any function. |
| </para> |
| </note> |
| </refsect1> |
| |
| |
| <refsect1 id="sql-createcast-examples"> |
| <title>Examples</title> |
| |
| <para> |
| To create a cast from type <type>text</type> to type |
| <type>int4</type> using the function <literal>int4(text)</literal>: |
| <programlisting> |
| CREATE CAST (text AS int4) WITH FUNCTION int4(text); |
| </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-compatible |
| types or extra arguments to implementation functions. |
| <literal>AS IMPLICIT</> is a <productname>PostgreSQL</productname> |
| extension, too. |
| </para> |
| </refsect1> |
| |
| |
| <refsect1 id="sql-createcast-seealso"> |
| <title>See Also</title> |
| |
| <para> |
| <xref linkend="sql-createfunction" endterm="sql-createfunction-title">, |
| <xref linkend="sql-createtype" endterm="sql-createtype-title">, |
| <xref linkend="sql-dropcast" endterm="sql-dropcast-title"> |
| </para> |
| </refsect1> |
| |
| </refentry> |