Aggregates in PostgreSQL

Question

Write an aggregate to count the number of times the number 40 is seen in a column.

Use your aggregate to count the number of 40 year olds in the directory table.

This is what I was doing:

Create function aggstep(curr int) returns int as $$
begin
    return curr.count where age = 40;
end;
$$ language plpgsql;

Create aggregate aggs(integer) (
stype = int,
initcond = '',
sfunc = aggstep);

Select cas(age) from directory;

Answer 1

You could do it for example like this:

First, create a transition function:

CREATE FUNCTION count40func(bigint, integer) RETURNS bigint
   LANGUAGE sql IMMUTABLE CALLED ON NULL INPUT AS
'SELECT $1 + ($2 IS NOT DISTINCT FROM 40)::integer::bigint';

That works because FALSE::integer is 0 and TRUE::integer is 1.

I use IS NOT DISTINCT FROM rather than = so that it does the correct thing for NULLs.

The aggregate can then be defined as

CREATE AGGREGATE count40(integer) (
   SFUNC = count40func,
   STYPE = bigint,
   INITCOND = 0
);

You can then query like

SELECT count40(age) FROM directory;