Using a domain

From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Using a domain
Date: 2011-12-01 00:39:04
Message-ID: CA+mi_8YKpPY-vokL6GKZnzAUoViDoHkawZp==2e3+LyKUOXYwA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I'm trying to use a domain to define a data type constraint, let's say
an hypothetical uk_post_code with pattern LNNLL. I'd enforce no
whitespaces, all uppercase.

I would also need a way to normalize before validate: given an input
such as "w3 6bq", normalize it to W36BQ before trying to apply the
check. It would be great if I could give this function the same name
of the domain, so that uk_post_code('w3 6bq') would return W36BQ cast
to the domain.

Unfortunately it seems a domain implicitly defines a function, and
this function only perform the cast: the above is thus equivalent to
'w3 6bq'::uk_post_code, which would fail as the constraint doesn't
match. IIRC from when I've played with type definitions in C, for a
type there is no such automatic definition: a function converting text
to the type must be explicitly provided. \df doesn't show such
function for the domain (nor DROP FUNCTION seems knowing it), and if I
create one, it is not invoked (the cast takes precedence).

Is there any way to define a conversion in a function call
uk_post_code(text), or the only way to provide a normalization
function is to give it a different name (such as to_uk_post_code - I'd
like to know if there is a convention in how to name this function).

Is there any documentation about domains apart from the
CREATE/ALTER/DELETE commands? Haven't found any in the docs.

Thanks.

-- Daniele

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2011-12-01 00:49:46 Re: Using a domain
Previous Message Tomas Vondra 2011-12-01 00:03:13 Re: Limiting number of connections to PostgreSQL per IP (not per DB/user)?