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/