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
> 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
> SELECT * FROM account; would be rewritten as
> SELECT * FROM (SELECT name, code FROM account JOIN status USING (id)) AS
> 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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
In response to
- Re: enums at 2005-10-28 03:07:19 from Rod Taylor
- Re: enums at 2005-10-28 18:57:03 from Rod Taylor
pgsql-hackers by date
|Next:||From: Jim C. Nasby||Date: 2005-10-28 18:23:27|
|Subject: Re: enums|
|Previous:||From: Jim C. Nasby||Date: 2005-10-28 18:04:03|
|Subject: Re: TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)",|