Re: enums - Mailing list pgsql-hackers

From Jim C. Nasby
Subject Re: enums
Date
Msg-id 20051028182051.GJ13187@pervasive.com
Whole thread Raw
In response to Re: enums  (Rod Taylor <pg@rbt.ca>)
Responses Re: enums
List pgsql-hackers
On Thu, Oct 27, 2005 at 11:07:19PM -0400, 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.
> 
> 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.

I'm not quiet following the WITH SURROGATE bit, but what you've
described certainly looks valuable. Note that I would still want to be
able to get at the raw numeric values in some fasion.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


pgsql-hackers by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)",
Next
From: "Jim C. Nasby"
Date:
Subject: Re: enums