Re: ENUM type - Mailing list pgsql-hackers

On Wed, Jul 27, 2005 at 12:11:47AM +0200, Jochem van Dieten wrote:
> On 7/26/05, Jim C. Nasby wrote:
> > On Tue, Jul 26, 2005 at 01:09:11PM -0700, Jeff Davis wrote:
> >>
> >> Ultimately to do it in a general way I think we'd need functions that
> >> return a type that can be used in a table definition. Aside from the
> >> many problems I don't know about, there are two other problems:
> >> (1) After the table (or column?) is dropped, we need to drop the type.
> >> (2) Functions currently don't support variable numbers of arguments, so
> >> enum still wouldn't be simple. We could do something kinda dumb-looking
> >> like:
> >> CREATE TABLE mytable (
> >>   color ENUM("red,green,blue,orange,purple,yellow");
> >> );
> >> And have the hypothetical ENUM function then parse the single argument
> >> and return a type that could be used by that table.

> ENUM is a braindead idea implemented because MySQL lacked the
> infrastructure to let its users do the right thing. (Lets face it:
> what percentage of the use of ENUM in MySQL would simply evaporate if
> MySQL implemented a proper BOOLEAN datatype?) PostgreSQL has the
> infrastructure to allow its users to do the right thing.

Sorry, I should have been more clear. There is the MySQL migration issue
with their braindead enum, but what I was wondering about is creating a
'type' that is a rollup for:

- create parent table with int id field and text and indexes
- add RI to base table
- add triggers/views/rules/other glue to make the id field hidden and transparent to users in normal uses

In other words, for the common use case of a table that has a field that
can contain a relatively limited number of values, provide an easy means
to normalize those values out into a seperate table and allow
applications to use the text values as if the table was de-normalized.

The reason I cross-posted to hackers was to get an answer to the
question of how difficult it would be to allow the database to deal with
a type definition that involves some arbitrary number of variables, as
shown above in the color example.

Also, are there any external hooks for DDL? If there were then it should
be possible to add support for an enum type that creates the required
tables, views/rules, etc without modifying the backend.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"