Decibel! wrote:
The last time I worked on a project where we had C code access the
database, we added stuff to map C enums to ints in the database (along
with a parent table to store the enum label). ISTM it'd be good if we
had a way to get the numeric index out of an enum.
If you mean here that you want to find the position in the ordering of
an enum value, it would be trivial to write, searching in the results
from enum_range().
Next cycle it might be worth adding a column to pg_enum with the
explicit order. My serious worry, though, is that it might lead people
to think they could alter that column and thereby change the ordering,
which of course they can't. (Quite apart from anything else, a mutable
ordering would play havoc with enums used in indexes.)
The technique of using a lookup table that you seem to refer to doesn't
need any special support from the catalogs or the type system. It's used
today in millions of applications. But it can have quite a high cost in
extra joins required to get the labels and extra application complexity.
For a case where the values in the domain of labels are truly fixed,
enums offer a much more performant and much simpler way to go.
I also like the idea
of having a fixed ordering to the labels in an enum.
I do not understand what this sentence means. The ordering *is* fixed -
it is defined by the order in which the labels are given in the create
type statement. And the oids that are assigned to the values in pg_enum
are sorted before being assigned to the labels precisely so that they
reflect this ordering. So rest assured that a given enum type will have
a fixed ordering, and it will be consistent across a dump/restore. What
will not necessarily be consistent is the actual oids used, making the
oids unsuitable for use in binary output as noted upthread.
One other very small observation: afaict, there appears to be no way
to list enum contents in psql (although you can list the enums
themselves in the type listing). Maybe this should be possible? I'm
willing to take a stab at these things if Andrew is busy.
Is there an SRF that will return this info? ISTM you should be able to
get the labels programmatically as well as via psql.
Maybe you need to read
http://developer.postgresql.org/pgdocs/postgres/functions-enum.html to
see info we have made available. We fully expect this list of functions
to grow as we discover how enums are used in practice.
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
