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 <dal...@solfertje.student.utwente.nl> wrote:
From: Alban Hertroys <dal...@solfertje.student.utwente.nl>
Subject: Re: [GENERAL] When is an explicit cast necessary?
To: "Alan Millington" <admilling...@yahoo.co.uk>
Cc: "Greg Smith" <g...@2ndquadrant.com>, "Postgres general mailing list"
<pgsql-general@postgresql.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
