Re: SELECT CAST(123 AS char) -> 1 - Mailing list pgsql-general

[Way behind on reading stuff - so I hope this wasn't covered later]

On Tue, 12 Feb 2008, Tom Lane wrote:

> Ken Johanson <pg-user@kensystem.com> writes:
> > For sake of interoperability (and using an API that requires String-type
> > hashtable keys), I'm trying to find a single CAST (int -> var/char)
> > syntax that works between the most databases. Only char seems to be a
> > candidate, but in 8.3 casting from an integer outputs only the first char...
>
> > Is this a bug, or would someone like to horrify me by stating something
> > like "spec says this is correct". :-)
>
> Okay: the spec says this is correct.
>
> SQL92 section 6.1 <data type> quoth
>
>          <character string type> ::=
>                 CHARACTER [ <left paren> <length> <right paren> ]
>               | CHAR [ <left paren> <length> <right paren> ]
>
>          ...
>
>          4) If <length> is omitted, then a <length> of 1 is implicit.
>
> Therefore, writing just "char" is defined as equivalent to "char(1)".
>
> Also, section 6.10 <cast specification> defines an explicit cast to
> a fixed-length string type as truncating or padding to the target
> length (LTD):
>
>               Case:
>
>               i) If the length in characters of SV is equal to LTD, then TV
>                  is SV.
>
>              ii) If the length in characters of SV is larger than LTD, then
>                  TV is the first LTD characters of SV. If any of the re-
>                  maining characters of SV are non-<space> characters, then a
>                  completion condition is raised: warning-string data, right
>                  truncation.
>
>             iii) If the length in characters M of SV is smaller than LTD,
>                  then TV is SV extended on the right by LTD-M <space>s.

Are you sure that's the correct section to be using? Isn't that 6.10
General Rules 5c which is if the source type is a fixed or variable
length character string? Wouldn't the correct place for an int->char
conversion be 5a or am I misreading it?

         5) If TD is fixed-length character string, then let LTD be the
            length in characters of TD.

            Case:

            a) If SD is exact numeric, then let YP be the shortest character
              string that conforms to the definition of <exact numeric
              literal> in Subclause 5.3, "<literal>", whose scale is the
              same as the scale of SD and whose interpreted value is the
              absolute value of SV.

              If SV is less than 0, then let Y be the result of

                 '-' | YP

              Otherwise, let Y be YP.

              Case:

              i) If Y contains any <SQL language character> that is not
                 in the repertoire of TD, then an exception condition is
                 raised: data exception-invalid character value for cast.

             ii) If the length in characters LY of Y is equal to LTD, then
                 TV is Y.

            iii) If the length in characters LY of Y is less than LTD, then
                 TV is Y extended on the right by LTD-LY <space>s.

             iv) Otherwise, an exception condition is raised: data exception-
                 string data, right truncation.