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

On Wed, Nov 25, 2009 at 12:15 AM, John Oyler <john(dot)m(dot)oyler(at)gmail(dot)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