Getting value from array in a SELECT statement

Question

Is it possible to define an array of text fields (or any data type) and select a value from it, all within a single statement? For example:

SELECT ARRAY['First', 'Second', 'Third'][mytable.state] as interpreted_state WHERE mytable.id = 1;

Answer 1

SELECT * FROM mytable.id WHERE columns IN ("1","2","3");

if I understood correctly what you meant..

Answer 2

You can sort-of do that with a SQL "CASE" statement, no?

SELECT CASE mytable.state 
  WHEN 0 THEN 'First'
  WHEN 1 THEN 'Second'
  WHEN 2 THEN 'Third'
   END 
  FROM mytable
 WHERE mytable.id = 1

Answer 3

That is really a silly way to do it. Have a lookup table and use a join.

Answer 4

You're close, you just need some parens.

SELECT (array['one','two','three'])[state]
FROM mytable
WHERE id = 1;

But as already stated, the CASE statement is the standard and portable method.