<para>

<command>CREATE CAST</command> defines a new cast. A cast

specifies how to perform a conversion between

two data types. For example:

two data types. For example,

<programlisting>

SELECT CAST(42 AS float8);

</programlisting>

</para>

<para>

You can define a cast as an <firstterm>I/O conversion cast</> using

You can define a cast as an <firstterm>I/O conversion cast</>byusing

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 result to the input function of the target data type.

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>

<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

result to the input function of the target data type.

resulting string to the input function of the target data type.

</para>

</listitem>

</varlistentry>

<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

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 steps, one to

to a type that uses a type modifier involves twocaststeps, one to

convert between data types and a second to apply the modifier.

</para>

syntax.

</para>

</note>

<note>

<para>

There's an exception to the exception, too: 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</> or <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>

The arguments can optionally have names attached.

See <xref linkend="sql-syntax-calling-funcs"> for details.

</para>

<note>

<para>

A function that takes a single argument of composite type can

optionally be called using field-selection syntax, and conversely

field selection can be written in functional style. That is, the

notations <literal>col(table)</> and <literal>table.col</> are

interchangeable. This behavior is not SQL-standard but is provided

in <productname>PostgreSQL</> because it allows use of functions to

emulate <quote>computed fields</>. For more information see

<xref linkend="xfunc-sql-composite-functions">.

</para>

</note>

</sect2>

<sect2 id="syntax-aggregates">

</para>

</sect2>

<sect2>

<sect2 id="xfunc-sql-composite-functions">

<title><acronym>SQL</acronym> Functions on Composite Types</title>

<para>

<literal>double_salary</> isn't a real column of the table.

(You can also emulate computed fields with views.)

</para>

<para>

Because of this behavior, it's unwise to give a function that takes

a single composite-type argument the same name as any of the fields of

that composite type.

</para>

</tip>

<para>

cpathtype=find_coercion_pathway(targetType,sourceType,

COERCION_EXPLICIT,

&cfuncid);

iscoercion= (cpathtype==COERCION_PATH_RELABELTYPE||

cpathtype==COERCION_PATH_COERCEVIAIO);

switch (cpathtype)

{

caseCOERCION_PATH_RELABELTYPE:

iscoercion= true;

break;

caseCOERCION_PATH_COERCEVIAIO:

if ((sourceType==RECORDOID||

ISCOMPLEX(sourceType))&&

TypeCategory(targetType)==TYPCATEGORY_STRING)

iscoercion= false;

iscoercion= true;

break;

default:

iscoercion= false;

break;

}

}

if (iscoercion)

(3 rows)

rollback;

--

-- We allow I/O conversion casts from composite types to strings to be

-- invoked via cast syntax, but not functional syntax. This is because

-- the latter is too prone to be invoked unintentionally.

--

select cast (fullname as text) from fullname;

fullname

----------

(0 rows)

select fullname::text from fullname;

fullname

----------

(0 rows)

select text(fullname) from fullname; -- error

ERROR: function text(fullname) does not exist

LINE 1: select text(fullname) from fullname;

^

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

select fullname.text from fullname; -- error

ERROR: column fullname.text does not exist

LINE 1: select fullname.text from fullname;

^

-- same, but RECORD instead of named composite type:

select cast (row('Jim', 'Beam') as text);

row

------------

(Jim,Beam)

(1 row)

select (row('Jim', 'Beam'))::text;

row

------------

(Jim,Beam)

(1 row)

select text(row('Jim', 'Beam')); -- error

ERROR: function text(record) does not exist

LINE 1: select text(row('Jim', 'Beam'));

^

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

select (row('Jim', 'Beam')).text; -- error

ERROR: could not identify column "text" in record data type

LINE 1: select (row('Jim', 'Beam')).text;

^

select*from price;

rollback;

select cast (fullnameastext)from fullname;

select fullname::textfrom fullname;

selecttext(fullname)from fullname;-- error

selectfullname.textfrom fullname;-- error

select cast (row('Jim','Beam')astext);

select (row('Jim','Beam'))::text;

selecttext(row('Jim','Beam'));-- error

select (row('Jim','Beam')).text;-- error