Re: Custom Data Type Question - Mailing list pgsql-hackers

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