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

SELECT (ENUM_RANGE(NULL::bnfunctionstype))[s]
FROM   generate_series(1, 5) s

Answer 2

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)

Answer 3

create function bnfunctionstype_from_number(int)
    returns bnfunctionstype
    immutable strict language sql as
$$
    select case ?
        when 0 then  normal 
        when 1 then  library 
        when 2 then  import 
        when 3 then  thunk 
        when 4 then  adjustor_thunk 
        else null
    end
$$;

友情链接