Re: Pulling data from a constraint def

On Tue, May 11, 2010 at 9:38 PM, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com> wrote:
> I have a constraint defined on a table....
>
>
>
> constraint design_style_is_invalid check (design_style in
> ('rls','sdp','rf','ssa','rom','rpt','analog','sdprpt','clkdist','global')),
>
>
>
> Is there a way to get the valid values in the list from the metadata
> somehow?  Specifically, when someone hits this constraint, I want to not
> oonly tell them they entered an invalid value, but give them the list of
> valid choices at the same time.

It shouldn't be too difficult to get the constraint definition out of
the pg_catalog tables (specifically pg_constraint). I used the "-E"
flag to psql to have it show me how "\d tablename" pulled the
constraint definitions, and it gave me something like this (you should
test whatever PG version you're using with psql -E, the following is
from 9.0beta1):

-- Find table OID:
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(design)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

-- Find constraint names and definitions for the table returned
-- above with OID 16391:
SELECT r.conname, pg_catalog.pg_get_constraintdef(r.oid, true)
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '16391' AND r.contype = 'c'
ORDER BY 1

which returns:

design_style_is_invalid | CHECK (design_style = ANY
(ARRAY['rls'::text, 'sdp'::text, 'rf'::text, 'ssa'::text, 'rom'::text,
'rpt'::text, 'analog'::text, 'sdprpt'::text, 'clkdist'::text,
'global'::text]))

for me. You should be able to parse the ARRAY[...] text to present
your users with valid choices from there.

> I’d rather not put these in a table and implement with a foreogn key
> constraint for performance reasons. (Does that make sense?)

I think this is quite reasonable, as long as your list of acceptable
design styles rarely changes and is reasonably small.

Josh