From 4d72af6cb31baa8d92bc86955ea7e5c4bc57eaad Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 9 Jan 2002 23:38:06 +0000 Subject: [PATCH] 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. --- doc/src/sgml/syntax.sgml | 188 ++++++++++++++++++++++++++++++++++------------- 1 file changed, 138 insertions(+), 50 deletions(-) diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 48c5e7bb97..9c14bde93f 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,5 +1,5 @@ @@ -370,7 +370,7 @@ REAL '1.23' -- string style 'string'::type CAST ( 'string' AS type ) - The value inside the string is passed to the input conversion + The string's text is passed to the input conversion routine for the type called type. The result is a constant of the indicated type. The explicit type cast may be omitted if there is no ambiguity as to the type the @@ -383,25 +383,23 @@ CAST ( 'string' AS type ) It is also possible to specify a type coercion using a function-like syntax: -typename ( value ) +typename ( 'string' ) - although this only works for types whose names are also valid as - function names. For example, double precision - can't be used this way, but the equivalent float8 - can. Also, the names interval, time, and - timestamp can only be used in this context if they are - double-quoted, because of parser conflicts. Therefore, the use of - the function-like cast syntax leads to inconsistencies and should - probably be avoided in new applications. + but not all type names may be used in this way; see for details. The ::, CAST(), and - function-call syntaxes can also be used to specify the type of - arbitrary expressions, but the form - type - 'string' can only be used to specify - the type of a literal constant. + function-call syntaxes can also be used to specify runtime type + conversions of arbitrary expressions, as discussed in . But the form + type 'string' + can only be used to specify the type of a literal constant. + Another restriction on + type 'string' + is that it does not work for array types; use :: + or CAST() to specify the type of an array constant. @@ -793,64 +791,50 @@ CAST ( 'string' AS type ) - A column reference + A column reference. - An operator invocation: - - expression operator expression (binary infix operator) - operator expression (unary prefix operator) - expression operator (unary postfix operator) - - where operator follows the syntax - rules of or is one of the - tokens AND, OR, and - NOT. Which particular operators exist and whether - they are unary or binary depends on what operators have been - defined by the system or the user. - describes the built-in operators. + A positional parameter reference, in the body of a function declaration. -( expression ) - Parentheses are used to group subexpressions and override precedence. + An operator invocation. - A positional parameter reference, in the body of a function declaration. + A function call. - A function call + An aggregate expression. - An aggregate expression + A type cast. - A scalar subquery. This is an ordinary - SELECT in parentheses that returns exactly one - row with one column. It is an error to use a subquery that - returns more than one row or more than one column in the context - of a value expression. (But if, during a particular execution, the - subquery returns no rows, the scalar result is taken to be NULL.) - The subquery can refer to variables from the surrounding query, - which will act as constants during any one evaluation of the subquery. - See also . + A scalar subquery. + + + + +( expression ) + + Parentheses are used to group subexpressions and override precedence. @@ -885,14 +869,14 @@ CAST ( 'string' AS type ) the key words NEW or OLD. (NEW and OLD can only appear in the action portion of a rule, while other correlation names can be used in any SQL statement.) - The correlation name can be omitted if the column name is unique + The correlation name and separating dot may be omitted if the column name + is unique across all the tables being used in the current query. If column is of an array type, then the optional subscript selects a specific - element in the array. If no subscript is provided, then the whole - array is selected. Refer to the description of the particular - commands in the PostgreSQL Reference Manual - for the allowed syntax in each case. + element or elements in the array. If no subscript is provided, then the + whole array is selected. (See for more about + arrays.) @@ -924,6 +908,26 @@ CREATE FUNCTION dept (text) RETURNS dept + Operator Invocations + + + There are three possible syntaxes for an operator invocation: + + expression operator expression (binary infix operator) + operator expression (unary prefix operator) + expression operator (unary postfix operator) + + where the operator token follows the syntax + rules of or is one of the + tokens AND, OR, and + NOT. Which particular operators exist and whether + they are unary or binary depends on what operators have been + defined by the system or the user. + describes the built-in operators. + + + + Function Calls @@ -973,7 +977,7 @@ sqrt(2) where aggregate_name is a previously defined aggregate, and expression is - any expression that does not itself contain an aggregate + any value expression that does not itself contain an aggregate expression. @@ -1006,6 +1010,90 @@ sqrt(2) + + Type Casts + + + data types + type casts + + + + A type cast specifies a conversion from one datatype to another. + PostgreSQL accepts two equivalent syntaxes + for type casts: + +CAST ( expression AS type ) +expression::type + + The CAST syntax conforms to SQL92; the syntax with + :: is historical PostgreSQL + usage. + + + + When a cast is applied to a value expression of a known type, it + represents a run-time type conversion. The cast will succeed only + if a suitable type conversion function is available. Notice that this + is subtly different from the use of casts with constants, as shown in + . A cast applied to an + unadorned string literal represents the initial assignment of a type + to a literal constant value, and so it will succeed for any type + (if the string literal's contents are acceptable input syntax for the + datatype). + + + + An explicit type cast may be omitted if there is no ambiguity as to the + type that a value expression must produce (for example, when it is + assigned to a table column); the system will automatically apply a + type cast in such cases. + + + + It is also possible to specify a type cast using a function-like + syntax: + +typename ( expression ) + + However, this only works for types whose names are also valid as + function names. For example, double precision + can't be used this way, but the equivalent float8 + can. Also, the names interval, time, and + timestamp can only be used in this fashion if they are + double-quoted, because of parser conflicts. Therefore, the use of + the function-like cast syntax leads to inconsistencies and should + probably be avoided in new applications. + + + + + Scalar Subqueries + + + A scalar subquery is an ordinary + SELECT in parentheses that returns exactly one + row with one column. The SELECT query is executed + and the single returned value is used in the surrounding value expression. + It is an error to use a query that + returns more than one row or more than one column as a scalar subquery. + (But if, during a particular execution, the subquery returns no rows, + there is no error; the scalar result is taken to be NULL.) + The subquery can refer to variables from the surrounding query, + which will act as constants during any one evaluation of the subquery. + See also . + + + + For example, the following finds the largest city population in each + state: + +SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) +FROM states; + + + + -- 2.11.0