Is there a way to fetch a specific column based on the text from a variable.
For instance you have a table with a, b, c
You want to query table d, e, f
Column c stores the field name of e or f
SELECT a, b, CAST(c as ?) AS e_or_f FROM table1 JOIN table2
--
Obviously you could use an if/else or case operator but not having to write that out would be nice.
Thanks
In this case, what you really want is to use CASE, possibly mixed with a CTE or an inline view. For example:
SELECT CASE WHEN 'foo' = myvar THEN foo
WHEN 'bar' = myvar THEN bar
WHEN 'baz' = myvar THEN baz
FROM foobarbaz
CROSS JOIN (SELECT ?::text AS myvar) v;
In order for a query to be efficient, the engine needs to know precisely what columns the query is about to return, because the query plan may greatly depend upon it.
With structures like CASE, there's a certain level of dynamicity in the query, but the query planner can still have an idea about what columns may need to be retrieved in one case or another before the query is executed. With a truly dynamic approach, when a column name is supposed to be retrieved from another column, variable or query parameter, it would be extremely difficult, if ever possible, to come up with a really efficient query plan.
So, you really need to let the query planner know about the required data at the time of compiling the query. Therefore, either use CASE/inline IF/what-have-you, or return both columns and choose between the two in the calling application as necessary.
