Re: extensible enum types - Mailing list pgsql-hackers

On Fri, 2010-06-18 at 12:34 -0400, Andrew Dunstan wrote:
>
> Robert Haas wrote:
> > On Fri, Jun 18, 2010 at 11:50 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
> >
> >> Then set the
> >> first value at  8 * p, then next at 9* p and so on. This is designed to
> >> allow more space to add labels at the beginning and end of the list, where
> >> this is more likely. Adding a label would be a matter of finding the labels
> >> adjacent to the position where we want to add the new label, and placing it
> >> half way between them, possibly with special rules for the list ends. If we
> >> want to add the label between two labels having values n and n+1 the
> >> addition would fail.
> >>
> >
> > I like the idea of being able to modify enums on the fly, but I'm
> > skeptical of an implementation that won't always work.  Maybe it's
> > still better than what we have now, but it seems grotty.
> >
> >
>
> I'd be perfectly happy to hear a reasonable alternative. Assuming we use
> some integer representation, given two labels represented by n and n+1,
> we can't add a label between them without rewriting the tables that use
> the type, whether it's my representation scheme or some other. Maybe we
> could have a FORCE option which would rewrite if necessary.

I apologize as this thread has already moved past the initial proposal.
I had mail problems so I didn't see any part of the thread in my client
until now.

My standard response to enums is, don't use them. Specifically because
you can't modify them. When I talk to developers and they see we have
enums they get excited and then I tell them you can't modify them and
they get very downtrodden. I tell them just to use a look up table.

Anyway, Andrew if you can find a reasonable way to make them modifiable,
I am all for it :)

On that note and yes this would be weird but have we considered some
kind of wrapper around just a lookup table? I mean what if there was a
system table that listed :

nspname, relation, column, value

the "type" enum would know to look in that table for valid values

Then we just add various syntactical sugar to manage the values in the
table via that specific enum:

ALTER TABLE foo ALTER COLUMN bar VALUES (bar,baz,bing,foo)

ALTER TABLE would know its an enum and would perform proper operations
on the system table.

Sincerely,

Joshua D. Drake


>
> cheers
>
> andrew
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering