-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Ralph Graulich Sent: Tuesday, January 10, 2012 3:47 PM To: David Johnston Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: (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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
