Aggregating boolean arrays in Postgres

Wednesday 26^th August 2009

We use arrays of booleans - a non-standard Postgres feature - quite frequently for storing permissions and similar data. It’s an elegant way to denormalize potentially scary schemas, as long as your client libraries support them.

We needed a function to count the number of true values across a whole table’s worth of boolean arrays - basically a histogram of how many true values in each array field. I thought it would be useful to share because I use PL/PgSQL so occasionally I always forget how to write it, and examples are useful.

Here’s the code:

CREATE OR REPLACE FUNCTION boolean_array_count(INTEGER[], boolean[]) 
RETURNS integer[] AS
$BODY$
DECLARE
    r INTEGER[];
    size INTEGER;
BEGIN
    IF $2 IS NULL THEN
        return $1;
    END IF;
    size := max(coalesce(array_upper($1, 1), 1), array_upper($2, 1));
    FOR i IN 1..size LOOP
        IF $2[i] = true THEN
            r[i] = coalesce($1[i], 0) + 1;
        ELSE
            r[i] = coalesce($1[i], 0);
        END IF;
    END LOOP;
    RETURN r;
END;
$BODY$ 
LANGUAGE 'plpgsql' VOLATILE;

CREATE AGGREGATE boolean_array_count (boolean[]) (
    SFUNC = boolean_array_count,
    STYPE = integer[]
);

You also need an implementation of the inexplicably missing max(integer, integer) function:

CREATE OR REPLACE FUNCTION max(integer, integer) RETURNS integer AS
$BODY$
BEGIN
    IF $1 > $2 THEN
        RETURN $1;
    END IF;
    RETURN $2;
END;
$BODY$ LANGUAGE 'plpgsql' IMMUTABLE STRICT SECURITY DEFINER;