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;
Add a comment
|
4 Answers
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
Comments
SELECT * FROM mytable.id WHERE columns IN ("1","2","3");
if I understood correctly what you meant..
1 Comment
John
I have an integer field (state in the above example) which represents a state. I'd like to return a textual representation of the state rather than the integer value. However, the textual representation is different depending on the id.
That is really a silly way to do it. Have a lookup table and use a join.
Comments
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.
