Greg Mitchell wrote:
> I'm trying to create a custom data type similar to an enumeration
> type. However, I'd like the mapping of the int<->string to be dynamic
> instead of hard coded. I'd like to have a table that contains this
> mapping that can be appended to. Creating this type is not very
> difficult. However, for performance reasons, I'd like to cache the
> mapping so that the table is only queried once every connection unless
> it changes. I'm thinking a combination of a flag that can be triggered
> on insert and a transaction id could be used to decide if the table
> needs to be reloaded. Unfortunately, I'm not exactly sure how to get
> started on this, any ideas?
>
>
Are you aware that there is a patch for first class enumeration types
waiting to be reviewed for 8.3? The mapping is kept entirely internal,
and you should never see what it is kept as underneath. It does not
provide for dynamically extending the enumeration set, for various
reasons, but there is an easy workaround, namely to create a new type
with the extra member(s) and then do:
alter table foo alter column bar type newtype using bar::newtype;
My little enumkit tool allows you to create enumerations today very
easily, but its values are completely hardcoded. However, the above
trick still works. The downside is that each enumeration type requires a
tiny bit of compilation.
cheers
andrew