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;