Converting an Integer to Enum in PostgreSQL

Question

I have created a custom data type enum like so:

create type "bnfunctionstype" as enum ( 
    'normal', 
    'library', 
    'import', 
    'thunk', 
    'adjustor_thunk' 
);

From an external data source I get integers in the range [0,4]. I'd like to convert these integers to their corresponding enum values.

How can I do this?

I'm using PostgreSQL 8.4.

Answer 1

If you have an enum like this:

CREATE TYPE payment_status AS ENUM ('preview', 'pending', 'paid', 
                                    'reviewing', 'confirmed', 'cancelled');

You can create a list of valid items like this:

SELECT i, (enum_range(NULL::payment_status))[i] 
  FROM generate_series(1, array_length(enum_range(NULL::payment_status), 1)) i

Which gives:

 i | enum_range 
---+------------
 1 | preview
 2 | pending
 3 | paid
 4 | reviewing
 5 | confirmed
 6 | cancelled
(6 rows)