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.
SELECT (ENUM_RANGE(NULL::bnfunctionstype))[s]
FROM generate_series(1, 5) s
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)
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
$$;
