Skip to content

Commit 4d72af6

Browse files
tglsfdctglsfdc
committed
Describe type casts under the heading of Value Expressions, and explain
the difference between a run-time type cast and casting a literal string to a specific type. Minor editorial work in same area.
1 parent 5461983 commit 4d72af6

File tree

1 file changed

+138
-50
lines changed

1 file changed

+138
-50
lines changed

‎doc/src/sgml/syntax.sgml

Lines changed: 138 additions & 50 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.56 2002/01/07 02:29:13 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.57 2002/01/09 23:38:06 tgl Exp $
33
-->
44

55
<chapter id="sql-syntax">
@@ -370,7 +370,7 @@ REAL '1.23' -- string style
370370
'<replaceable>string</replaceable>'::<replaceable>type</replaceable>
371371
CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
372372
</synopsis>
373-
The value inside the string is passed to the input conversion
373+
The string's text is passed to the input conversion
374374
routine for the type called <replaceable>type</replaceable>. The
375375
result is a constant of the indicated type. The explicit type
376376
cast may be omitted if there is no ambiguity as to the type the
@@ -383,25 +383,23 @@ CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
383383
It is also possible to specify a type coercion using a function-like
384384
syntax:
385385
<synopsis>
386-
<replaceable>typename</replaceable> ( <replaceable>value</replaceable> )
386+
<replaceable>typename</replaceable> ( '<replaceable>string</replaceable>' )
387387
</synopsis>
388-
although this only works for types whose names are also valid as
389-
function names. For example, <literal>double precision</literal>
390-
can't be used this way, but the equivalent <literal>float8</literal>
391-
can. Also, the names <literal>interval</>, <literal>time</>, and
392-
<literal>timestamp</> can only be used in this context if they are
393-
double-quoted, because of parser conflicts. Therefore, the use of
394-
the function-like cast syntax leads to inconsistencies and should
395-
probably be avoided in new applications.
388+
but not all type names may be used in this way; see <xref
389+
linkend="sql-syntax-type-casts"> for details.
396390
</para>
397391

398392
<para>
399393
The <literal>::</literal>, <literal>CAST()</literal>, and
400-
function-call syntaxes can also be used to specify the type of
401-
arbitrary expressions, but the form
402-
<replaceable>type</replaceable>
403-
'<replaceable>string</replaceable>' can only be used to specify
404-
the type of a literal constant.
394+
function-call syntaxes can also be used to specify runtime type
395+
conversions of arbitrary expressions, as discussed in <xref
396+
linkend="sql-syntax-type-casts">. But the form
397+
<replaceable>type</replaceable> '<replaceable>string</replaceable>'
398+
can only be used to specify the type of a literal constant.
399+
Another restriction on
400+
<replaceable>type</replaceable> '<replaceable>string</replaceable>'
401+
is that it does not work for array types; use <literal>::</literal>
402+
or <literal>CAST()</literal> to specify the type of an array constant.
405403
</para>
406404
</sect3>
407405

@@ -793,64 +791,50 @@ CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
793791

794792
<listitem>
795793
<para>
796-
A column reference
794+
A column reference.
797795
</para>
798796
</listitem>
799797

800798
<listitem>
801799
<para>
802-
An operator invocation:
803-
<simplelist>
804-
<member><replaceable>expression</replaceable> <replaceable>operator</replaceable> <replaceable>expression</replaceable> (binary infix operator)</member>
805-
<member><replaceable>operator</replaceable> <replaceable>expression</replaceable> (unary prefix operator)</member>
806-
<member><replaceable>expression</replaceable> <replaceable>operator</replaceable> (unary postfix operator)</member>
807-
</simplelist>
808-
where <replaceable>operator</replaceable> follows the syntax
809-
rules of <xref linkend="sql-syntax-operators"> or is one of the
810-
tokens <token>AND</token>, <token>OR</token>, and
811-
<token>NOT</token>. Which particular operators exist and whether
812-
they are unary or binary depends on what operators have been
813-
defined by the system or the user. <xref linkend="functions">
814-
describes the built-in operators.
800+
A positional parameter reference, in the body of a function declaration.
815801
</para>
816802
</listitem>
817803

818804
<listitem>
819-
<synopsis>( <replaceable>expression</replaceable> )</synopsis>
820805
<para>
821-
Parentheses are used to group subexpressions and override precedence.
806+
An operator invocation.
822807
</para>
823808
</listitem>
824809

825810
<listitem>
826811
<para>
827-
A positional parameter reference, in the body of a function declaration.
812+
A function call.
828813
</para>
829814
</listitem>
830815

831816
<listitem>
832817
<para>
833-
A function call
818+
An aggregate expression.
834819
</para>
835820
</listitem>
836821

837822
<listitem>
838823
<para>
839-
An aggregate expression
824+
A type cast.
840825
</para>
841826
</listitem>
842827

843828
<listitem>
844829
<para>
845-
A scalar subquery. This is an ordinary
846-
<command>SELECT</command> in parentheses that returns exactly one
847-
row with one column. It is an error to use a subquery that
848-
returns more than one row or more than one column in the context
849-
of a value expression. (But if, during a particular execution, the
850-
subquery returns no rows, the scalar result is taken to be NULL.)
851-
The subquery can refer to variables from the surrounding query,
852-
which will act as constants during any one evaluation of the subquery.
853-
See also <xref linkend="functions-subquery">.
830+
A scalar subquery.
831+
</para>
832+
</listitem>
833+
834+
<listitem>
835+
<synopsis>( <replaceable>expression</replaceable> )</synopsis>
836+
<para>
837+
Parentheses are used to group subexpressions and override precedence.
854838
</para>
855839
</listitem>
856840
</itemizedlist>
@@ -885,14 +869,14 @@ CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
885869
the key words <literal>NEW</literal> or <literal>OLD</literal>.
886870
(NEW and OLD can only appear in the action portion of a rule,
887871
while other correlation names can be used in any SQL statement.)
888-
The correlation name can be omitted if the column name is unique
872+
The correlation name and separating dot may be omitted if the column name
873+
is unique
889874
across all the tables being used in the current query. If
890875
<replaceable>column</replaceable> is of an array type, then the
891876
optional <replaceable>subscript</replaceable> selects a specific
892-
element in the array. If no subscript is provided, then the whole
893-
array is selected. Refer to the description of the particular
894-
commands in the <citetitle>PostgreSQL Reference Manual</citetitle>
895-
for the allowed syntax in each case.
877+
element or elements in the array. If no subscript is provided, then the
878+
whole array is selected. (See <xref linkend="arrays"> for more about
879+
arrays.)
896880
</para>
897881
</sect2>
898882

@@ -923,6 +907,26 @@ CREATE FUNCTION dept (text) RETURNS dept
923907
</para>
924908
</sect2>
925909

910+
<sect2>
911+
<title>Operator Invocations</title>
912+
913+
<para>
914+
There are three possible syntaxes for an operator invocation:
915+
<simplelist>
916+
<member><replaceable>expression</replaceable> <replaceable>operator</replaceable> <replaceable>expression</replaceable> (binary infix operator)</member>
917+
<member><replaceable>operator</replaceable> <replaceable>expression</replaceable> (unary prefix operator)</member>
918+
<member><replaceable>expression</replaceable> <replaceable>operator</replaceable> (unary postfix operator)</member>
919+
</simplelist>
920+
where the <replaceable>operator</replaceable> token follows the syntax
921+
rules of <xref linkend="sql-syntax-operators"> or is one of the
922+
tokens <token>AND</token>, <token>OR</token>, and
923+
<token>NOT</token>. Which particular operators exist and whether
924+
they are unary or binary depends on what operators have been
925+
defined by the system or the user. <xref linkend="functions">
926+
describes the built-in operators.
927+
</para>
928+
</sect2>
929+
926930
<sect2>
927931
<title>Function Calls</title>
928932

@@ -973,7 +977,7 @@ sqrt(2)
973977

974978
where <replaceable>aggregate_name</replaceable> is a previously
975979
defined aggregate, and <replaceable>expression</replaceable> is
976-
any expression that does not itself contain an aggregate
980+
any value expression that does not itself contain an aggregate
977981
expression.
978982
</para>
979983

@@ -1006,6 +1010,90 @@ sqrt(2)
10061010
</para>
10071011
</sect2>
10081012

1013+
<sect2 id="sql-syntax-type-casts">
1014+
<title>Type Casts</title>
1015+
1016+
<indexterm>
1017+
<primary>data types</primary>
1018+
<secondary>type casts</secondary>
1019+
</indexterm>
1020+
1021+
<para>
1022+
A type cast specifies a conversion from one datatype to another.
1023+
<productname>PostgreSQL</productname> accepts two equivalent syntaxes
1024+
for type casts:
1025+
<synopsis>
1026+
CAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable> )
1027+
<replaceable>expression</replaceable>::<replaceable>type</replaceable>
1028+
</synopsis>
1029+
The <literal>CAST</> syntax conforms to SQL92; the syntax with
1030+
<literal>::</literal> is historical <productname>PostgreSQL</productname>
1031+
usage.
1032+
</para>
1033+
1034+
<para>
1035+
When a cast is applied to a value expression of a known type, it
1036+
represents a run-time type conversion. The cast will succeed only
1037+
if a suitable type conversion function is available. Notice that this
1038+
is subtly different from the use of casts with constants, as shown in
1039+
<xref linkend="sql-syntax-constants-generic">. A cast applied to an
1040+
unadorned string literal represents the initial assignment of a type
1041+
to a literal constant value, and so it will succeed for any type
1042+
(if the string literal's contents are acceptable input syntax for the
1043+
datatype).
1044+
</para>
1045+
1046+
<para>
1047+
An explicit type cast may be omitted if there is no ambiguity as to the
1048+
type that a value expression must produce (for example, when it is
1049+
assigned to a table column); the system will automatically apply a
1050+
type cast in such cases.
1051+
</para>
1052+
1053+
<para>
1054+
It is also possible to specify a type cast using a function-like
1055+
syntax:
1056+
<synopsis>
1057+
<replaceable>typename</replaceable> ( <replaceable>expression</replaceable> )
1058+
</synopsis>
1059+
However, this only works for types whose names are also valid as
1060+
function names. For example, <literal>double precision</literal>
1061+
can't be used this way, but the equivalent <literal>float8</literal>
1062+
can. Also, the names <literal>interval</>, <literal>time</>, and
1063+
<literal>timestamp</> can only be used in this fashion if they are
1064+
double-quoted, because of parser conflicts. Therefore, the use of
1065+
the function-like cast syntax leads to inconsistencies and should
1066+
probably be avoided in new applications.
1067+
</para>
1068+
</sect2>
1069+
1070+
<sect2>
1071+
<title>Scalar Subqueries</title>
1072+
1073+
<para>
1074+
A scalar subquery is an ordinary
1075+
<command>SELECT</command> in parentheses that returns exactly one
1076+
row with one column. The <command>SELECT</command> query is executed
1077+
and the single returned value is used in the surrounding value expression.
1078+
It is an error to use a query that
1079+
returns more than one row or more than one column as a scalar subquery.
1080+
(But if, during a particular execution, the subquery returns no rows,
1081+
there is no error; the scalar result is taken to be NULL.)
1082+
The subquery can refer to variables from the surrounding query,
1083+
which will act as constants during any one evaluation of the subquery.
1084+
See also <xref linkend="functions-subquery">.
1085+
</para>
1086+
1087+
<para>
1088+
For example, the following finds the largest city population in each
1089+
state:
1090+
<programlisting>
1091+
SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
1092+
FROM states;
1093+
</programlisting>
1094+
</para>
1095+
</sect2>
1096+
10091097
</sect1>
10101098

10111099

0 commit comments

Comments
 (0)