Aggregating boolean arrays in Postgres

Posted on August 26th, 2009 by Russ. Filed under Databases.

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;

Trackback URI

Leave a Reply


About Me

I build infrastructure.

I currently work for Smarkets as Head of Tech Operations. Before that I worked at Last.fm. I also co-founded the London Hackspace.

I live in London and sometimes moonlight as a freelance photographer.

Links

Projects