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

John Oyler wrote:

   I can create one or more domains, and use those to create the
   composite type from. But each domain can only be constrained in its
   own value, I can't constrain element #1's value based on what element
   #2's value is.

   If I create a domain from a composite, it will complain with a "ERROR:
   cdt.instant_message is not a valid base type for a domain" error.

   I can't create a trigger for a composite either (technically it's not
   a table, but I was hoping it might be treated as one internally).

   Is there any way to do this? Are there any plans to change or expand
   this functionality in the future?

   Thanks,
   John O.

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?

Thanks,
John O.

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

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

Never fails. About 5 minutes after posting a URL, my cable modem
decides to get a new IP (having kept it since May).

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

On Wed, Nov 25, 2009 at 12:15 AM, John Oyler <john.m.oyler@gmail.com> wrote:
> 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.

it doesn't do what you want.  'create table' does (and has for a
while) create a type that backs the table.  To me, tables are types so
I find the whole create type as feature kinda redundant with create
table.  It does have some minor technical advantages though...for
example each table you create is another record for autovacuum to
scan.

The advantage of create table is that you can manipulate the fields as
you can with tables, especially adding new ones.  This is a big
advantage so if your types accumulate new fields once in a while you
many want to think about using create table.  I think that 'create
type as' should just be a proxy to 'create table...with storage=no',
or something like that...you shouldn't be forced to choose between
annoying autovacuum and altering a type down the line.  I always
create types via create table.

IMO, ('abc', 'def')::foo honestly should honor constraints on foo.
This probably isn't controversial.  The way things work with composite
types currently is a product of a gradual evolution...the feature is
amazingly useful, but not everything makes 100% sense.  No big deal.

Since starting with 8.4 you can index composite types directly, you
could possibly make a case that a lot of built in postgresql types,
especially geometric types, could be reimplemented as composites, and
toss a lot of code that backs the geo types such as the various in/out
routines.  I don't think this will ever happen for various reasons
(backwards compatibility and possibly efficiency for example), but
it's fun to think about.  The best way to do complex validation based
on type in postgresql today is in C if you don't want to add
constraints wherever the type is used.

merlin

On Wed, Nov 25, 2009 at 1:02 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>
> IMO, ('abc', 'def')::foo honestly should honor constraints on foo.
> This probably isn't controversial.

So who do we need to convince to make this happen? The things we could
do with this kick ass. Maybe the details of the file I linked are
wrong, but just the idea of a short script that we could settle on as
a standard... (on the other hand, I just saw a varchar(30) for a
column named birth_date at work the other day, and we already have a
data type perfect for that, so maybe it wouldn't help at all.)

> Since starting with 8.4 you can index composite types directly, you
> could possibly make a case that a lot of built in postgresql types,
> especially geometric types, could be reimplemented as composites, and
> toss a lot of code that backs the geo types such as the various in/out
> routines.  I don't think this will ever happen for various reasons
> (backwards compatibility and possibly efficiency for example), but
> it's fun to think about.  The best way to do complex validation based
> on type in postgresql today is in C if you don't want to add
> constraints wherever the type is used.

Well, my understanding is that anything in C means that the superuser
has to declare/define it. That makes things slightly more
inconvenient, but isn't a dealbreaker.

Are there any examples of this anywhere? Could someone cook one up for
me? The docs are pretty thin on this subject, or I'm looking in the
wrong places.

Thanks,
John O.