CODEWITHSUNDEEP
sqlpostgresqleval
Sergey Onishchenko
Sergey Onishchenko

Reputation: 7851

Evaluate expression as a column name

I would like to use the result of an expression as a column name.

For example I have a table "cbr" with the only row

--QUERY_1
SELECT * FROM cbr;

 value_usd | value_uah | value_kzt | value_rur 
-----------+-----------+-----------+-----------
   57.0861 |   2.15257 |  0.171365 |         1


--QUERY_2
SELECT value_usd from cbr;

 value_usd 
-----------
   57.0861


--QUERY_3
SELECT 'value_'||'usd' from cbr;

 ?column?  
-----------
 value_usd
(1 row)

I would like QUERY_3 to return the same result as QUERY_2. How can this be done?

Upvotes: 0

Views: 635

Answers (2)

danjuggler
danjuggler

Reputation: 1320

The best choice is to normalize the table structure by adding a column for currency and a row for each value as @Clodoaldo Neto stated. If this can't be done, dynamic SQL is you next best option. Note that it must be used inside a pl/pgsql code. Here's an example:

CREATE OR REPLACE FUNCTION GetCurrencyValue(Currency text)
RETURNS SETOF DOUBLE PRECISION AS
$BODY$
BEGIN
  RETURN QUERY EXECUTE 'SELECT value_'||Currency||' from cbr;';
END;
$BODY$
LANGUAGE plpgsql;

This function takes in the parameter "Currency" and uses it as part of the column name. You can then get the result of your second query by calling this function:

--Updated QUERY_3
SELECT * FROM GetCurrencyValue('usd');

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

Normalize the table:

create table cbr (
    currency char(3),
    value numeric
);

insert into cbr (currency, value) values 
    ('usd',57.0861),('uah',2.15257),('kzt',0.171365),('rur',1)
;

select *
from cbr
where currency = 'usd'
;
 currency |  value  
----------+---------                                                                                                                                                      
 usd      | 57.0861

Upvotes: 1

Related Questions