On Fri, Sep 04, 2009 at 12:26:19PM -0500, Kevin Grittner wrote:
> Sam Mason <sam@samason.me.uk> wrote:
> > Kevin Grittner wrote:
> >> test=# select case when true then 'xxx' else 'a'::"char" end from t;
> >
> > 'xxx' is being used to initialize a value of "char" type.
>
> As I read the semantics of the CASE predicate, it returns one of the
> given values. 'x' is not one of the given values, regardless of type.
You seem to be confused about the difference between literals and
values. Maybe a different example:
SELECT '00001'::int;
I get '1' back from that, and not '00001'. This is because '00001' is
the literal that is parsed into a value of type integer and then the
query is run and this same value is asked to convert itself back into a
literal to be written out to the screen.
Back to your example; you're asking PG to interpret the literal 'xxx'
as a "char" and it does that (but doesn't give any error back when it
chucks data away). This behavior may be confusing because for text
types the literal exactly the same as the value itself, but this is only
a very specific behavior of text types. For example, '{"1"}', '{1}'
and even '{"+001"}' are all literal representations of identical integer
arrays.
> I don't think an error is the right thing, I think returning the
> specified value is the right thing. I don't think it's a good thing
> that the type system decides that the result type for this case
> predicate is "char" and that 'xxx' needs to be coerced to that type.
I fail to see how an error isn't the right thing; if we try with some
other types let see if you think any of these should succeed.
SELECT CASE WHEN TRUE THEN text 'xxx' ELSE 0 END;
SELECT CASE WHEN TRUE THEN text 'xxx' ELSE TRUE END;
SELECT CASE WHEN TRUE THEN text 'xxx' ELSE '{1}'::INT[] END;
SELECT CASE WHEN TRUE THEN text 'xxx' ELSE array [1] END;
"char" is no different other than, by default, it happens to look a lot
like any value of text type. It's a different type (that happens to
have some implicit casts to confuse things) and hence I can't see why
invalid literals should not be thrown out.
--
Sam http://samason.me.uk/