Thread: Possible 7.1RC1 bug

Here's a fragment of code that works on 7.0.3 but gives a type mismatch on 7.1:

Under 7.1RC1, func1 fails and func2 builds OK.  The error is:

ERROR:  return type mismatch in function: declared to return int4, returns numeric

It appears that sum(int4) returns type numeric.  Shouldn't it return int4?
 

create table ints (
    inte        int4
);

create function func1() returns int4 as '
    select coalesce(sum(inte),0) from ints;
' language 'sql';

create function func2() returns int4 as '
    select coalesce(sum(inte)::int4,0) from ints;
' language 'sql';
 
 

Kyle <kyle@actarg.com> writes:
> It appears that sum(int4) returns type numeric.  Shouldn't it return
> int4?

This is not a bug.  It's a deliberate change in behavior: sum() and
avg() on integer datatypes now return numeric, to avoid problems with
overflow.
        regards, tom lane

Tom,

> This is not a bug.  It's a deliberate change in behavior: sum() and
> avg() on integer datatypes now return numeric, to avoid problems with
> overflow.

That could throw off some of my already-written views & functions.  Is
there a setting that can change this back to the "regular" behavior of
sum(integer) = integer?

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

"Josh Berkus" <josh@agliodbs.com> writes:
>> This is not a bug.  It's a deliberate change in behavior: sum() and
>> avg() on integer datatypes now return numeric, to avoid problems with
>> overflow.

> That could throw off some of my already-written views & functions.  Is
> there a setting that can change this back to the "regular" behavior of
> sum(integer) = integer?

Sorry, no.  You can coerce the result back to int4 if you care to risk
overflow: "select sum(int4field)::int4" or some such.
        regards, tom lane