views:

164

answers:

2

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.

+1  A: 
SELECT (ENUM_RANGE(NULL::bnfunctionstype))[s]
FROM   generate_series(1, 5) s
Quassnoi
This looks extremely elegant - I'll try it on monday (when I'm back at the office) and credit you for an answer...
BuschnicK
A: 
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
$$;
Tometzky
I need to do this for several enum types, so I'd really like to avoid repeating all the individual values and creating a stored procedure for each.
BuschnicK