Rod Taylor wrote:
The other issue is ease of use.
We used lookup tables in bugzilla when it was converted to work with
Postgres. But many users will find having to do that annoying, to say
the least. I think there's a very good case for providing true enums.
Then why did you use lookup tables instead of a varchar and a
constraint? Probably performance.
To be honest, I forget why. Possible because we also needed to be able
to get a list of allowed values, although I don't know how one does that
in mysql. Maybe because it just seemed like a good idea at the time and
nobody spoke up against it.
A much more general purpose but just as good solution would be the
ability to create a hidden surrogate key for a structure.
CREATE TABLE status (code varchar(20) PRIMARY KEY) WITH SURROGATE;
CREATE TABLE account (name varchar(60), status varchar(20) references
status);
Behind the scenes (transparent to the user) this gets converted to:
CREATE TABLE status (id SERIAL UNIQUE, code varchar(20) PRIMARY KEY)
WITH SURROGATE;
CREATE TABLE account (name varchar(60), status integer references
status(id));
SELECT * FROM account; would be rewritten as
SELECT * FROM (SELECT name, code FROM account JOIN status USING (id)) AS
account;
Enum might be good for a short list of items but something like the
above should be good for any common value that we manually create
surrogate keys for today but without the clutter or the application
needing to know.
If PostgreSQL had an updatable view implementation it would be pretty
simple to implement.
That won't make it easier to change the ordering or the value set, which
some people seem concerned about.
But it too might be a nice feature. I suspect it would be a lot more
work than simple enums, for which there is significant demand.
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
