Re: Re: I need help creating a composite type with some sort of constraints.

On Tue, Nov 24, 2009 at 10:13 AM, John Oyler <john(dot)m(dot)oyler(at)gmail(dot)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