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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
