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

"Michaud, Everett" <EMichaud (AT) sequoianet (DOT) com> writes:
int8, a/k/a bigint. Or if you really want to return int4, you could
cast the count() result to int4.

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

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

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).

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/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster