Postgresql Constraint or Trigger on sum of Array Column Values

Question

Suppose I the following postgresql table donation_allocations

donation_id : int | constituent_project_ids: ARRAY(int) | allocation_in_bps: ARRAY(int)
---------------------------------------------------------------------------------------
0                 | (1, 2, 3)                           | (1000, 5000, 4000)
1                 | (2, 4)                              | (5000, 5000)

where allocation_in_bps is the distribution of the donation money in the projects in bps (0.01%). (So, 5000 stands for 50%). I want to make sure that in each new row inserted into the table, the allocations sums to 10000, that is all money of the donations is allotted to some of the projects.

How can I write constraint or trigger that checks for that?

Answer 1

That would be a check constraint, but you need a helper function for it:

CREATE FUNCTION array_sum(integer[]) RETURNS integer
   LANGUAGE sql IMMUTABLE STRICT AS
'SELECT sum(e) FROM unnest($1) AS a(e)';

ALTER TABLE donation_allocations
   ADD CHECK (array_sum(allocation_in_bps) = 10000);

This is a trick to get a subquery into a check constraint, but it is alright because the function is immutable.