Hi Greg
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.
A simpler way to do this might be to only cache the list per query
context. In your IO functions, you could whack a pointer to your cache
onto fcinfo->flinfo->fn_extra, and the same flinfo gets passed in for
e.g. all output function calls for that column for that query, IIRC.
This was what I had in mind originally when I did the enum patch, but I
ended up just using syscaches, which I think would be unavailable to you
writing a UDT.
The upside of the above is that for a given query, the contents of your table shouldn't change, so there's no mucking
aboutwith trying to
keep things in other backends up to date. The downside is that you have
to do the lookup per query, but if you're dealing with lots of data then
it'll get dwarfed by the actual query, and if not, who cares?
The other question that leaps to mind is whether you want to have more
than one of these types. If you do, you may have to have multiple
versions of the IO functions, otherwise e.g. your output function might
be passed the value 0, but was that the 0 representing the 'red' string
from the rgb enum, or the 'northern' string from the hemisphere enum?
You don't know, and postgresql won't tell you directly.
There are a few ways around this. In your case, it might be ok to
compile different versions of the IO functions for each enum which point
to different tables, or the same table with a discriminator. Or you
could see the various different proposals when my patch was first
discussed. See the thread starting at
http://archives.postgresql.org/pgsql-hackers/2006-08/msg00979.php or if
you want a peek at the patch, see
http://archives.postgresql.org/pgsql-patches/2006-09/msg00000.php. A
rather simpler starting point might be Andrew's enumkit
http://www.oreillynet.com/pub/a/databases/2006/01/06/enumerated-fields-in-postgresql.html?page=last&x-showcontent=text,
or possibly Martijn's tagged types at
http://svana.org/kleptog/pgsql/taggedtypes.html.
Cheers
Tom
