Re: multi-row check constraints?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> Hi, I was wondering what is the best way to achieve a multi-row check
> constraint. For example, you have a table with two columns: ID and
> percent, no primary key. The goal is to enforce that all values of
> percent, per ID, add up to exactly 100%. I come from an Oracle
> background, and what you would probably do on Oracle is create a
> materialized view with the sum(percent) grouped by ID, then put a
> constraint on the sum column. This problem is also solvable using
> triggers, but it's messy and imposes a lot of serialization. Not to
> mention easy to get wrong.

I don't think the triggers solution is that bad. The only trick is using
an intermediate table so that we don't have to recheck the entire table
at the end of the statement:

CREATE TABLE hundred (
id INTEGER NULL,
percent FLOAT NOT NULL
);

CREATE TABLE tracker (
trackid INTEGER
);

CREATE FUNCTION percent_one() RETURNS TRIGGER
LANGUAGE plpgsql
AS $_$
BEGIN
IF TG_OP <> 'INSERT' THEN
INSERT INTO tracker VALUES (OLD.id);
END IF;
IF TG_OP <> 'DELETE' THEN
INSERT INTO tracker VALUES (NEW.id);
END IF;
RETURN NULL;
END;
$_$;

CREATE FUNCTION percent_two() RETURNS TRIGGER
LANGUAGE plpgsql
AS $_$
DECLARE
myrec RECORD;
badids INTEGER = 0;
BEGIN
FOR myrec IN
SELECT id, sum(percent) AS pc FROM hundred
WHERE EXISTS (SELECT 1 FROM tracker WHERE trackid = id)
GROUP BY id
HAVING sum(percent) <> 100
ORDER BY id
LOOP
RAISE WARNING 'Percentage on id % sums to %, not 100', myrec.id, myrec.pc;
badids = badids + 1;
END LOOP;
TRUNCATE TABLE tracker;
IF badids>=1 THEN
RAISE EXCEPTION 'Number of ids not summing to 100: %', badids;
END IF;
RETURN NULL;
END;
$_$;

CREATE TRIGGER percent_one AFTER INSERT OR UPDATE OR DELETE ON hundred
FOR EACH ROW EXECUTE PROCEDURE percent_one();

CREATE TRIGGER percent_two AFTER INSERT OR UPDATE OR DELETE ON hundred
FOR EACH STATEMENT EXECUTE PROCEDURE percent_two();

- -- Fails:
INSERT INTO hundred
SELECT 1,25 UNION ALL SELECT 1,25 UNION ALL SELECT 2,33;

- -- Works:
INSERT INTO hundred
SELECT 1,45 UNION ALL SELECT 1,55;

- -- Works:
UPDATE hundred SET id=2 where id=1;

- -- Fails:
UPDATE hundred SET percent=55.5 WHERE percent = 55;

- -- Works:
INSERT INTO hundred
SELECT 3,33.5 UNION ALL SELECT 3,55.5 UNION ALL SELECT 3,11.0;

- -- Fails:
DELETE FROM hundred WHERE percent = 55.5;

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200703222156
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFGAzQ1vJuQZxSWSsgRA9WIAKCXf3t3MkSj2xoXLoScx3lu0aBwQQCfUiTW
is9ZKyAPuzaAvnkMjP0dXEc=
=BeQC
-----END PGP SIGNATURE-----