Andrus Andrus - 7 months ago 63
SQL Question

How to evaluate expression in select statement in Postgres

Postgres 9.1+ database contains customers and product.
In customers table, customer price is described as sql expression in priceexpression column for every customer.

How to create price list from this data ?
I tried code below but got error since eval() is undefined.

create table customer
( id int primary key,
priceexpression text );
insert into customer values (1, 'price*0.95'),(2,'cost+12.0' );

create table product
( id char(20) primary key,
price numeric(12,4),
cost numeric(12,4) );
insert into product values ('PRODUCT1', 120, 80),('PRODUCT2', 310.5, 290);


select
customer.id as customer,
product.id as product,
eval(priceexpression) as price
from customer,product


This is ASP.NET MVC4 application.

Answer

You can't do this with plain SQL in PostgreSQL.

You'll need to use dynamic SQL with PL/PgSQL's EXECUTE statement. See PL/PgSQL and the many examples here on Stack Overflow.

Loop over the result set and EXECUTE 'SELECT '||the_expression INTO resultvar; for each row.

Be aware that this is a massive security hole if anyone who isn't truested to run raw SQL can possibly modify the price column. You should really not do this.