Re: (check) constraints on composite type

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ralph Graulich
Sent: Tuesday, January 10, 2012 3:47 PM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] (check) constraints on composite type

Let's assume I have three fields in my custom type:

field1 varchar(4)   // or text
field2 smallint
field3 char(1)      // or text

Now I want to add a complex check constraint involving the combination of
those three fields, written in pseudo code:

field1 IN ('A') && field2 BETWEEN 1 AND 120 && field3 IS NULL
field1 IN ('B') && field2 BETWEEN 1 AND 40 && field3 IS NULL
field1 IN ('TFnr') && field3 BETWEEN 1600 AND 2200 && field3 IN ('a', 'b',
'c')
field1 IS NULL & field2 = 1 & field3 IN ('a'..'z') OR field3 IS NULL
...            & field2 = 2 & field3 IN ('a'..'h') OR field3 IS NULL
...            & field2 = 3 & field3 IN ('a'..'o', 'Z') OR field3 IS NULL

and so on (the problem is actually far more complex, just to give a hint
of.). My point is putting all the validation of data into the database
itself, so just having to care for changes at one place and making sure data
and data domains of valid data are tightly integrated, which I favor.

Regards,
     Ralph

----------------------------------------------------------------------------
---

I would suggest trying to make your constraints as "template" as possible
and then create a table that provides inputs for each of the constraint
"parameters".  You include a field which flags the constraint as either
"require" or "disallow" and then you write a function that iterates over the
table and compares each of the constraints against the value and ensure that
at least of the "require" constraints pass and that all the "disallow"
constraints fail.

RegularExpressions will likely come in handy and you will probably need to
create "field_n_can_be_null" booleans to allow them explicitly.

The implementation I've used is to "CROSS JOIN" the value and the constraint
table and then use ARRAY_AGG to create multiple arrays for the various test
results (the specific set of arrays varies depending on your needs).  I've
done this with a single text field check using reqularexpressions and the
results work fairly well (ignoring performance concerns which have not yet
been a factor due to the lite load I experience).  For an exclusion
constraint I add "Exclude" if the constraint matches otherwise I add
"Ignore"; for inclusion constraints I add "Include" for matches or "Ignore";
I then exclude if "Exclude" = ANY(match_results) OR "Ignore" =
ALL(match_results); otherwise there has to be at least one "Include" and
thus the check passes.

For example:

SELECT val, CASE WHEN 'Exclude' = ANY(results) THEN 'Fail - Excluded'
          WHEN 'Ignore' = ALL(results) THEN 'Fail - No Includes
Match'
          WHEN 'Include' = ANY(results) THEN 'Include'
          ELSE 'Fail - Not Possible As Coded But Just In Case' END
AS test_outcome
FROM (
SELECT val, ARRAY_AGG( CASE WHEN val ~ pattern THEN CASE WHEN include THEN
'Include' ELSE 'Exclude' END ELSE 'Ignore' END ) AS results
FROM (VALUES ('value to check')) vals (val)
CROSS JOIN (VALUES ('test pattern',true) [, ...] ) tests (pattern, include)
GROUP BY val
) val_test

David J.