Just wondering what others have done for using enum or uuid columns in
exclusion constraints?
I have a solution now, but I just wanted to see what others have ended up
doing as well and see if what i'm doing is sane. If i'm doing something
unsafe, or you know of a better way, please chime in.
For enum columns, I use a function in the constraint to convert the enum
value to an oid. The function is defined as such:
> CREATE OR REPLACE FUNCTION enum_to_oid(
> _enum_schema text,
> _enum_name text,
> _enum anyenum)
> RETURNS oid AS
> $BODY$
> SELECT e.oid
> FROM pg_type t
> INNER JOIN pg_enum e
> ON t.oid = e.enumtypid
> INNER JOIN pg_catalog.pg_namespace n
> ON n.oid = t.typnamespace
> WHERE true
> AND n.nspname = _enum_schema
> AND t.typname = _enum_name
> AND e.enumlabel = _enum::text;
> $BODY$
> LANGUAGE sql STABLE;
For uuid columns, I use another function in the constraint to convert it to
a bytea type defined here:
> CREATE OR REPLACE FUNCTION uuid_to_bytea(_uuid uuid)
> RETURNS bytea AS
> $BODY$
> select decode(replace(_uuid::text, '-', ''), 'hex');
> $BODY$
> LANGUAGE sql IMMUTABLE;
And i'd use these functions in the constraint like this:
> CONSTRAINT claim_product_reason_code_active_range_excl EXCLUDE
> USING gist (uuid_to_bytea(claim_product_id) WITH =,
> enum_to_oid('enum'::text, 'claim_reason_type'::text, claim_reason_type)
> WITH =, enum_to_oid('enum'::text, 'claim_reason_code'::text,
> claim_reason_code) WITH =, active_range WITH &&)
And as a closing note on this, I really can't wait until these are
supported types for gist indexes. It would be great not to have to play
games like this to have exclusion constraints on my tables just because I
am using uuids instead of ints, and enums instead of lookup tables (when an
enum really fits the problem well).
