Re: When is an explicit cast necessary?

In C, if I declare a short variable shortvar, I can write an assignment shortvar = 1. The literal value 1 is an int, but the compiler will implicitly convert it to a short. Similarly, if I write a function func() which takes a short argument, then provided I use function prototypes, I can write a call func(1). Again the compiler will implicitly convert the int 1 to a short.
 
In the case of my PL/pgSQL function, the syntax that I have to use to call it
 
    select col1, col2, col3 from func(?, ?, ?) as (col1 int, col2 smallint, col3 char(1))
 
clearly indicates what I expect the return datatypes to be. There is no ambiguity. The only question is whether 1 can be converted to a smallint, and 'R' to char(1). Clearly the answer is Yes, as otherwise the explicit casts would fail. But it seems that with the change made in Postgres 8.3, I now have to write the type information twice. I do not regard that as an improvement!

--- On Sat, 10/4/10, Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:

From: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
Subject: Re: [GENERAL] When is an explicit cast necessary?
To: "Alan Millington" <admillington(at)yahoo(dot)co(dot)uk>
Cc: "Greg Smith" <greg(at)2ndquadrant(dot)com>, "Postgres general mailing list" <pgsql-general(at)postgresql(dot)org>
Date: Saturday, 10 April, 2010, 11:51

On 9 Apr 2010, at 16:57, Alan Millington wrote:

> However, every language that I have ever used will implicitly convert an integer 1 to a smallint (or short) 1 when required to do so. How can such a cast be called "surprising behaviour", or produce "misleading results", to quote the first article?

No, they probably don't cast integer values down to smallint. What they do is cast the smallint up to integer, as that's a safe cast. After all, numbers that fit in an int may not fit in a smallint (try "select 75000::smallint;" for example) and you'd lose data casting it down, but it's fine the other way around.

Since your function has smallint as one of its parameter types the database can't cast the smallint up to an int like it would normally do in such cases as the function doesn't accept integer values for that parameter. PG can't do much but throw an error.

Alban Hertroys