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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
