Re: SELECT CAST(123 AS char) -> 1

[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(at)kensystem(dot)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.