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 about with 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
