Postgres Pro
Downloads
Home   >   mailing lists

Thread: help using count in function

help using count in function

From
"Michaud, Everett"
Date:

Perhaps this is simple, but it has me stumped.  What is the data type to use for returning a value from count?

Example:

 

CREATE FUNCTION ev3(int4) RETURNS int4
       AS 'Select count(id) from events where id = $1;' LANGUAGE 'sql';

 

ERROR:  return type mismatch in function declared to return integer

 

I’ve tried this with text, character, numeric, etc.  Nothing seems to work. 

Any ideas?

Thanks,

Ev

Re: help using count in function

From
Tom Lane
Date:
"Michaud, Everett" <EMichaud@sequoianet.com> writes:
> Perhaps this is simple, but it has me stumped.  What is the data type to
> use for returning a value from count?

int8, a/k/a bigint.  Or if you really want to return int4, you could
cast the count() result to int4.

> ERROR:  return type mismatch in function declared to return integer

I don't think any modern version of Postgres is that stingy with
information about this error.  I get either

ERROR:  return type mismatch in function: declared to return integer, returns bigint

or

ERROR:  return type mismatch in function declared to return integer
DETAIL:  Actual return type is bigint.

depending on which version I try.

            regards, tom lane

Re: help using count in function

From
Michael Fuhr
Date:
On Thu, Oct 28, 2004 at 07:39:31PM -0400, Michaud, Everett wrote:

> What is the data type to use for returning a value from count?

You can find out by querying the system catalogs:

  test=> SELECT * FROM pg_aggregate WHERE aggfnoid = 'count'::regproc;
   aggfnoid | aggtransfn | aggfinalfn | aggtranstype | agginitval
  ----------+------------+------------+--------------+------------
   count    | int8inc    | -          |           20 | 0
  (1 row)

  test=> \df int8inc
                         List of functions
   Result data type |   Schema   |  Name   | Argument data types
  ------------------+------------+---------+---------------------
   bigint           | pg_catalog | int8inc | bigint
  (1 row)

The return type is BIGINT (aka INT8).

> CREATE FUNCTION ev3(int4) RETURNS int4
>        AS 'Select count(id) from events where id = $1;' LANGUAGE 'sql';
>
> ERROR:  return type mismatch in function declared to return integer

What version of PostgreSQL are you using?  7.4.5 and 8.0.0beta4
both provide a detail message:

ERROR:  return type mismatch in function declared to return integer
DETAIL:  Actual return type is bigint.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/