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
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)? |