Getting value from array in a SELECT statement
Is it possible to define an array of text fields (or any data type) and s开发者_如何转开发elect 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;
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
SELECT * FROM mytable.id WHERE columns IN ("1","2","3");
if I understood correctly what you meant..
That is really a silly way to do it. Have a lookup table and use a join.
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.
精彩评论