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,
cost numeric(12,4) );
insert into product values ('PRODUCT1', 120, 80),('PRODUCT2', 310.5, 290);
customer.id as customer,
product.id as product,
eval(priceexpression) as price
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.