Re: ENUM type

Jim C. Nasby wrote:

>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.
>
>

Your question assumes an implementation. My thought for enums instead
was that it might be nice to provide support for dynamically created
input/output functions for an enum type (written in, say, plperl or
plpgsql). I have no idea how feasible this is either, but it could be
quite nice.

cheers

andrew