Re: When is an explicit cast necessary?

I am using Postgres 8.4.1 on Windows XP Professional Service Pack 3.
 
I have a PL/pgSQL function which is defined as "returns record". The record contains three values. In one execution path, the values are read from a table, the selected columns being of types int, smallint and char(1). In another execution path, the second and third values are the literals 1 and 'R'. In the original version of the function the assignment in the second case was as follows:
 
   v_rv = (v_id, 1, 'R') ;

where v_rv is a variable of type record, and v_id is of type int. The client application calls the function as follows:
 
   select col1, col2, col3 from func(?, ?, ?) as (col1 int, col2 smallint, col3 char(1))
 
As far as I remember, when I was using Postgres 8.1.4 that worked, but under Postgres 8.4.1 it results in the errors "Returned type integer does not match expected type smallint" and "Returned type unknown does not match expected type character". I can avoid the error by altering the assignment thus:
 
   v_rv = (v_id, 1::smallint, 'R'::char(1)) ;

but I am puzzled as to why these explicit casts should be necessary. Is this covered anywhere in the documentation?
 

Alan Millington wrote:
>
> As far as I remember, when I was using Postgres 8.1.4 that worked, but
> under Postgres 8.4.1 it results in the errors "Returned type integer
> does not match expected type smallint" and "Returned type unknown does
> not match expected type character".
>

There was a major breaking change to how casting is handled in 8.3.
Some good reading about what happened:

http://www.depesz.com/index.php/2008/05/05/error-operator-does-not-exist-integer-text-how-to-fix-it/
http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us

Thank you for that helpful information. I thought I was going mad!
 
It would never have occurred to me to write a join which relied on an implicit cast between int and string. 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?

--- On Fri, 9/4/10, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

From: Greg Smith <greg(at)2ndquadrant(dot)com>
Subject: Re: [GENERAL] When is an explicit cast necessary?
To: "Alan Millington" <admillington(at)yahoo(dot)co(dot)uk>
Cc: "Postgres general mailing list" <pgsql-general(at)postgresql(dot)org>
Date: Friday, 9 April, 2010, 15:06

Alan Millington wrote:
>
> As far as I remember, when I was using Postgres 8.1.4 that worked, but under Postgres 8.4.1 it results in the errors "Returned type integer does not match expected type smallint" and "Returned type unknown does not match expected type character".
>

There was a major breaking change to how casting is handled in 8.3.  Some good reading about what happened:

http://www.depesz.com/index.php/2008/05/05/error-operator-does-not-exist-integer-text-how-to-fix-it/
http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html

-- Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com   www.2ndQuadrant.us

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?
>

SELECT ' 1'=(' 1'::numeric)::text;

That returns false, and is typical of the sort of surprising behavior
you can see if you just allow sloppy casts everywhere. Casting between
numeric and text types is not a lossless operation. Here's another one:

SELECT '01'=('01'::numeric)::text;

Also false. This variation has made my life difficult more than once
when inventory part numbers at a company were allowed to start with a
leading 0, and subqueries (such as you'll see when using a view) were
involved. You can have two queries that each work fine on their own,
but chain them together by making one run against a subquery of the
other and you can get mysteriously burned when things aren't equal the
way you expected.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us

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

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4bc0584910411899921361!

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

On Sat, Apr 10, 2010 at 05:15:18PM +0000, Alan Millington wrote:
> 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.

The problem is basically that postgres sees the 1 not as a literal but
as an integer, which can't be downcast to a smallint. If however you
wrote the literal as '1' (with quotes) postgres would happily downcast
it for you without any problem.

The question is: does the column really need to be smallint.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
> - Charles de Gaulle

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> The question is: does the column really need to be smallint.

Yeah. Usually, declaring a function's argument as int rather than
smallint is the easiest fix. We have looked into this in the past,
and concluded that the negative aspects of allowing integer constants
to implicitly cast to smallint parameters would outweigh the
positives. As an example, such simple expressions as "2 + 2" would
start to fail because it'd be unclear whether int or smallint addition
is meant. (And the point isn't academic, since for example it would
affect the overflow threshold.)

regards, tom lane