Re: Re: I need help creating a composite type with some sort of constraints. - Mailing list pgsql-general

On Tue, Nov 24, 2009 at 5:51 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Nov 24, 2009 at 10:13 AM, John Oyler <john.m.oyler@gmail.com> wrote:
>>>
>>> For now you have to put all checks in custom constructor functions.
>>>
>>> Scott
>>
>> I must not be looking in the right place... the only thing I can find that
>> seems remotely related, is the page on input/output functions for custom
>> types. And I was under the impression that only applies to non-composite
>> custom types. Did I miss something?
>
> no...he was basically saying that if you have a constraint that must
> be enforced at the type level, you have to go the custom type route.
>
> you can of course write both column constraints and table constraints
> that check composite types:
>
> create table foo(a text, b text);
> create table bar(f foo check ((f).a != 'test'));
>
> You may also write a table trigger for 'bar' to check the values of
> foo. However, constraints are only checked by the table that is
> storing the composite. Not on the composite itself (even it it is
> defined as a table with a constraint).
>
> Arguably:
> create table foz(a text, b text, check (a != 'test'));
> select ('test', 'test')::foz;
> should fail, but doesn't.
>
> merlin
>

Arguably, it should. I've been tinkering with Postgres these past few
weeks with an idea, probably dumb. This is what I have so far, for
those curious about it:

http://98.193.233.172/~john/custom_data_types.sql

I've created a few data types that some people might find useful I'd
like to think. And there are a few more that I'd like to add to it,
mostly to do with mailing addresses. I had hoped to create a
mailing_address composite type, that would have the standard columns
we're all familiar with. But for the region/territory/state element of
that, I need to be able to constrain it based on what value is there
for the country element. "Utah" is valid for "USA" but not if it's
"Great Britain". Zip codes and so forth could be handled in a similar
way.

Now, if I want constraints for each element, I merely declare a domain
for each with the appropriate check(), and then declare the composite
with "create type as" (or even with create table which you
demonstrated and I was unaware of). But that only works for each
element individually. The first domain can't be dependent on the value
of the second and so forth. The thing that seemed obvious to me would
be to create a composite type, and then create a domain based off of
that. But composite types aren't a valid base type for a domain, or so
the error message says.

And, since composite types aren't true tables, I can't write a trigger
either for it either that checks values (though, with the variations
you've shown me, I'll probably test those just to see what happens).

So, if anyone has any ideas how to make this work, it'd be really
nice. While the constraints can always go into the final table, that
means repeating them each time the column is declared in a new table,
something I had hoped to avoid.

Is there any call to change or add to this functionality in 8.5? Is
there something missing that will allow me to contrive this?

Thanks,
John O.

PS For tab-completion purposes, it strikes me that my schema name
could still be shorter. Is it ugly to just make it "c"?