From 31edbadf4af45dd4eecebcb732702ec6d7ae1819 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 5 Jun 2007 21:31:09 +0000 Subject: Downgrade implicit casts to text to be assignment-only, except for the ones from the other string-category types; this eliminates a lot of surprising interpretations that the parser could formerly make when there was no directly applicable operator. Create a general mechanism that supports casts to and from the standard string types (text,varchar,bpchar) for *every* datatype, by invoking the datatype's I/O functions. These new casts are assignment-only in the to-string direction, explicit-only in the other, and therefore should create no surprising behavior. Remove a bunch of thereby-obsoleted datatype-specific casting functions. The "general mechanism" is a new expression node type CoerceViaIO that can actually convert between *any* two datatypes if their external text representations are compatible. This is more general than needed for the immediate feature, but might be useful in plpgsql or other places in future. This commit does nothing about the issue that applying the concatenation operator || to non-text types will now fail, often with strange error messages due to misinterpreting the operator as array concatenation. Since it often (not always) worked before, we should either make it succeed or at least give a more user-friendly error; but details are still under debate. Peter Eisentraut and Tom Lane --- doc/src/sgml/catalogs.sgml | 17 ++++-- doc/src/sgml/ref/create_cast.sgml | 107 +++++++++++++++++++++++++++----------- doc/src/sgml/syntax.sgml | 39 ++++++++------ doc/src/sgml/typeconv.sgml | 84 +++++++++++++++++------------- 4 files changed, 161 insertions(+), 86 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 1bf95871c0e..aa699ae62e1 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,4 +1,4 @@ - + @@ -1358,11 +1358,22 @@ - The catalog pg_cast stores data type conversion paths, - both built-in paths and those defined with + The catalog pg_cast stores data type conversion + paths, both built-in paths and those defined with . + + It should be noted that pg_cast does not represent + every type conversion that the system knows how to perform; only those that + cannot be deduced from some generic rule. For example, casting between a + domain and its base type is not explicitly represented in + pg_cast. Another important exception is that + I/O conversion casts, those performed using a data type's own + I/O functions to convert to or from text or other string types, + are not explicitly represented in pg_cast. + + <structfield>pg_cast</> Columns diff --git a/doc/src/sgml/ref/create_cast.sgml b/doc/src/sgml/ref/create_cast.sgml index c6e944e26ae..c329c36b190 100644 --- a/doc/src/sgml/ref/create_cast.sgml +++ b/doc/src/sgml/ref/create_cast.sgml @@ -1,4 +1,4 @@ - + @@ -35,11 +35,11 @@ CREATE CAST (sourcetype AS targettype -SELECT CAST(42 AS text); +SELECT CAST(42 AS float8); - converts the integer constant 42 to type text by + converts the integer constant 42 to type float8 by invoking a previously specified function, in this case - text(int4). (If no suitable cast has been defined, the + float8(int4). (If no suitable cast has been defined, the conversion fails.) @@ -69,8 +69,7 @@ SELECT CAST(42 AS text); INSERT INTO foo (f1) VALUES (42); will be allowed if the cast from type integer to type - text is marked AS ASSIGNMENT, otherwise - not. + text is marked AS ASSIGNMENT, otherwise not. (We generally use the term assignment cast to describe this kind of cast.) @@ -78,19 +77,37 @@ INSERT INTO foo (f1) VALUES (42); If the cast is marked AS IMPLICIT then it can be invoked implicitly in any context, whether assignment or internally in an - expression. For example, since || takes text - operands, + expression. (We generally use the term implicit + cast to describe this kind of cast.) + For example, consider this query: -SELECT 'The time is ' || now(); +SELECT 2 + 4.0; - will be allowed only if the cast from type timestamp to - text is marked AS IMPLICIT. Otherwise it - will be necessary to write the cast explicitly, for example: + The parser initially marks the constants as being of type integer + and numeric respectively. There is no integer + + numeric operator in the system catalogs, + but there is a numeric + numeric operator. + The query will therefore succeed if a cast from integer to + numeric is available and is marked AS IMPLICIT — + which in fact it is. The parser will apply the implicit cast and resolve + the query as if it had been written -SELECT 'The time is ' || CAST(now() AS text); +SELECT CAST ( 2 AS numeric ) + 4.0; - (We generally use the term implicit - cast to describe this kind of cast.) + + + + Now, the catalogs also provide a cast from numeric to + integer. If that cast were marked AS IMPLICIT — + which it is not — then the parser would be faced with choosing + between the above interpretation and the alternative of casting the + numeric constant to integer and applying the + integer + integer 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 numeric-and-integer expression as + numeric; there is no built-in knowledge about that. @@ -208,9 +225,7 @@ SELECT 'The time is ' || CAST(now() AS text); 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.) + second argument. @@ -237,6 +252,32 @@ SELECT 'The time is ' || CAST(now() AS text); need to declare casts both ways explicitly. + + cast + I/O conversion + + + + It is normally not necessary to create casts between user-defined types + and the standard string types (text, varchar, and + char(n)). PostgreSQL will + automatically handle a cast to a string type by invoking the other + type's output function, or conversely handle a cast from a string type + by invoking the other type's input function. These + automatically-provided casts are known as I/O conversion + casts. I/O conversion casts to string types are treated as + assignment casts, while I/O conversion casts from string types are + explicit-only. You can override this behavior by declaring your own + cast to replace an I/O conversion 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.) + + Prior to PostgreSQL 7.3, every function that had the same name as a data type, returned that data type, and took one @@ -265,16 +306,20 @@ SELECT 'The time is ' || CAST(now() AS text); - There is one small lie in the preceding paragraph: there is still one - case in which pg_cast will be used to resolve the - meaning of an apparent function call. If a - function call name(x) matches no - actual function, but name is the name of a data type - and pg_cast shows a binary-compatible cast to this - type from the type of 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. + 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 name(x) does not + exactly match any existing function, but name is the name + of a data type and pg_cast provides a binary-compatible cast + to this type from the type of x, then the call will be + construed as a binary-compatible cast. This exception is made so that + binary-compatible casts can be invoked using functional syntax, even + though they lack any function. Likewise, if there is no + pg_cast 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. @@ -284,10 +329,10 @@ SELECT 'The time is ' || CAST(now() AS text); Examples - To create a cast from type text to type - int4 using the function int4(text): + To create a cast from type bigint to type + int4 using the function int4(bigint): -CREATE CAST (text AS int4) WITH FUNCTION int4(text); +CREATE CAST (bigint AS int4) WITH FUNCTION int4(bigint); (This cast is already predefined in the system.) diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index f1b5fe9411e..a869cd440aa 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,4 +1,4 @@ - + SQL Syntax @@ -561,18 +561,18 @@ CAST ( 'string' AS type ) The ::, CAST(), and function-call syntaxes can also be used to specify run-time 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 :: + linkend="sql-syntax-type-casts">. To avoid syntactic ambiguity, the + type 'string' + syntax can only be used to specify the type of a simple literal constant. + Another restriction on the + type 'string' + syntax is that it does not work for array types; use :: or CAST() to specify the type of an array constant. The CAST() syntax conforms to SQL. The - type 'string' + type 'string' syntax is a generalization of the standard: SQL specifies this syntax only for a few data types, but PostgreSQL allows it for all types. The syntax with @@ -1431,16 +1431,21 @@ CAST ( expression AS type double-quoted, because of syntactic conflicts. Therefore, the use of the function-like cast syntax leads to inconsistencies and should probably be avoided in new applications. - - (The function-like syntax is in fact just a function call. When - one of the two standard cast syntaxes is used to do a run-time - conversion, it will internally invoke a registered function to - perform the conversion. By convention, these conversion functions - have the same name as their output type, and thus the function-like - syntax is nothing more than a direct invocation of the underlying - conversion function. Obviously, this is not something that a portable - application should rely on.) + + + + The function-like syntax is in fact just a function call. When + one of the two standard cast syntaxes is used to do a run-time + conversion, it will internally invoke a registered function to + perform the conversion. By convention, these conversion functions + have the same name as their output type, and thus the function-like + syntax is nothing more than a direct invocation of the underlying + conversion function. Obviously, this is not something that a portable + application should rely on. For further details see + . + + diff --git a/doc/src/sgml/typeconv.sgml b/doc/src/sgml/typeconv.sgml index af4a8e916cb..c5373aa7533 100644 --- a/doc/src/sgml/typeconv.sgml +++ b/doc/src/sgml/typeconv.sgml @@ -1,4 +1,4 @@ - + Type Conversion @@ -139,7 +139,8 @@ and for the GREATEST and LEAST functions. The system catalogs store information about which conversions, called casts, between data types are valid, and how to perform those conversions. Additional casts can be added by the user -with the CREATE CAST command. (This is usually +with the +command. (This is usually done in conjunction with defining new data types. The set of casts between the built-in types has been carefully crafted and is best not altered.) @@ -336,28 +337,28 @@ Some examples follow. -Exponentiation Operator Type Resolution +Factorial Operator Type Resolution -There is only one exponentiation -operator defined in the catalog, and it takes arguments of type -double precision. -The scanner assigns an initial type of integer to both arguments -of this query expression: +There is only one factorial operator (postfix !) +defined in the standard catalog, and it takes an argument of type +bigint. +The scanner assigns an initial type of integer to the argument +in this query expression: -SELECT 2 ^ 3 AS "exp"; +SELECT 40 ! AS "40 factorial"; - exp ------ - 8 + 40 factorial +-------------------------------------------------- + 815915283247897734345611269596115894272000000000 (1 row) -So the parser does a type conversion on both operands and the query +So the parser does a type conversion on the operand and the query is equivalent to -SELECT CAST(2 AS double precision) ^ CAST(3 AS double precision) AS "exp"; +SELECT CAST(40 AS bigint) ! AS "40 factorial"; @@ -421,7 +422,7 @@ entries for the prefix operator @, all of which implement absolute-value operations for various numeric data types. One of these entries is for type float8, which is the preferred type in the numeric category. Therefore, PostgreSQL -will use that entry when faced with a non-numeric input: +will use that entry when faced with an unknown input: SELECT @ '-4.5' AS "abs"; abs @@ -429,9 +430,9 @@ SELECT @ '-4.5' AS "abs"; 4.5 (1 row) -Here the system has performed an implicit conversion from text to float8 -before applying the chosen operator. We can verify that float8 and -not some other type was used: +Here the system has implicitly resolved the unknown-type literal as type +float8 before applying the chosen operator. We can verify that +float8 and not some other type was used: SELECT @ '-4.5e500' AS "abs"; @@ -447,8 +448,8 @@ try a similar case with ~, we get: SELECT ~ '20' AS "negation"; ERROR: operator is not unique: ~ "unknown" -HINT: Could not choose a best candidate operator. You might need to add explicit -type casts. +HINT: Could not choose a best candidate operator. You might need to add +explicit type casts. This happens because the system cannot decide which of the several possible ~ operators should be preferred. We can help @@ -518,12 +519,24 @@ this step.) If no exact match is found, see whether the function call appears -to be a trivial type conversion request. This happens if the function call +to be a special type conversion request. This happens if the function call has just one argument and the function name is the same as the (internal) name of some data type. Furthermore, the function argument must be either -an unknown-type literal or a type that is binary-compatible with the named -data type. When these conditions are met, the function argument is converted -to the named data type without any actual function call. +an unknown-type literal, or a type that is binary-compatible with the named +data type, or a type that could be converted to the named data type by +applying that type's I/O functions (that is, the conversion is either to or +from one of the standard string types). When these conditions are met, +the function call is treated as a form of CAST specification. + + + The reason for this step is to support function-style cast specifications + in cases where there is not an actual cast function. If there is a cast + function, it is conventionally named after its output type, and so there + is no need to have a special case. See + + for additional commentary. + + @@ -670,30 +683,31 @@ The parser learns from the pg_cast catalog that text and varchar are binary-compatible, meaning that one can be passed to a function that accepts the other without doing any physical conversion. Therefore, no -explicit type conversion call is really inserted in this case. +type conversion call is really inserted in this case. -And, if the function is called with an argument of type integer, the parser will -try to convert that to text: +And, if the function is called with an argument of type integer, +the parser will try to convert that to text: SELECT substr(1234, 3); +ERROR: function substr(integer, integer) does not exist +HINT: No function matches the given name and argument types. You might need +to add explicit type casts. + + +This does not work because integer does not have an implicit cast +to text. An explicit cast will work, however: + +SELECT substr(CAST (1234 AS text), 3); substr -------- 34 (1 row) - -This actually executes as - -SELECT substr(CAST (1234 AS text), 3); - -This automatic transformation can succeed because there is an -implicitly invocable cast from integer to -text. -- cgit v1.2.3