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 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.



---------------------------(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

Reply via email to