From: MRB <nomail@example.com>
Subject: ENUM like data type
Date: Tue, 21 Jun 2005 17:20:19 +0200
Organization: Posted via Supernews, http://www.supernews.com
Message-ID: <11bgc1oblfasq3d@corp.supernews.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 40
To: pgsql-sql@postgresql.org

Hi All,

I have something in mind I'm not certain is do-able.

I'm working with a lot of data from MySQL where the MySQL ENUM type is used.

This is not a big problem per se but creating the proper lookup tables 
is becoming a bit tedious so I was hoping to make something better of it.

Here is where I get uncertain as to if this is possible. My idea is to 
create a pseudo type that triggers the creation of it's lookup tables 
the same way the SERIAL type triggers creation of a sequence and returns 
an int with the right default value.

Here is what would want to happen:

CREATE TABLE test(
    testfield ENUM('Bits', 'of', 'data')
);

-- This would trigger the following events:

CREATE TABLE test_testfield_enum(
     id VARCHAR(4) NOT NULL PRIMARY KEY;
);
INSERT INTO test_testfield_enum VALUES('Bits');
INSERT INTO test_testfield_enum VALUES('Of');
INSERT INTO test_testfield_enum VALUES('data');

CREATE TABLE test(
     testfield VARCHAR(4) REFERENCES test_testfield_enum(id)
);


Hope that is clear enough. As said I am not quite sure this is possible, 
my guess would be no. But any input is appreciated.

Cheers,

Martin