Skip site navigation (1) Skip section navigation (2)

Re: help using count in function

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: "Michaud, Everett" <EMichaud(at)sequoianet(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: help using count in function
Date: 2004-10-31 06:54:34
Message-ID: 20041031065434.GA54565@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
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/

In response to

pgsql-novice by date

Next:From: Michael FuhrDate: 2004-10-31 14:04:47
Subject: Re: using palloc in extension functions in c
Previous:From: Tom LaneDate: 2004-10-31 06:51:54
Subject: Re: help using count in function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group