Short story about PostgreSQL SUM function

Author:	Wojciech Muła
Added on:	2013-11-04

Here is a simple PostgreSQL type:

CREATE TYPE foo_t AS (
        id    integer,
        total bigint
);

and a simple query wrapped in a stored procedure:

CREATE FUNCTION group_foo()
        RETURNS SETOF foo_t
        LANGUAGE "SQL"
AS $$
        SELECT id, SUM(some_column) FROM some_table GROUP BY id;
$$;

Now, we want to sum everything:

CREATE FUNCTION total_foo()
        RETURNS bigint -- same as foo_t.total
        LANGUAGE "SQL"
AS $$
        SELECT SUM(total) FROM group_foo();
$$;

And we have an error about types inconsistency!

This is caused by SUM function — in PostgreSQL there are many variants of this function, as the db engine supports function name overriding (sounds familiar for C++ guys). There are following variants in PostgreSQL 9.1:

$ \df sum
                                                 List of functions
   Schema   | Name | Result data type | Argument data types | Type
------------+------+------------------+---------------------+------
 pg_catalog | sum  | numeric          | bigint              | agg
 pg_catalog | sum  | double precision | double precision    | agg
 pg_catalog | sum  | bigint           | integer             | agg
 pg_catalog | sum  | interval         | interval            | agg
 pg_catalog | sum  | money            | money               | agg
 pg_catalog | sum  | numeric          | numeric             | agg
 pg_catalog | sum  | real             | real                | agg
 pg_catalog | sum  | bigint           | smallint            | agg

Smaller types are promoted: from integer we get bigint, from bigint we get numeric, and so on.