How to evaluate expression in select statement in Postgres

Question

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.

Andreas Covidiot · Accepted Answer · 2016-04-30 08:49:15Z

1

you can write an SQL function that does this for you and use e.g. the ones supplied with postgres-utils:

select 
  c.name as cust_name,
  p.name as prod_name,
  p.cost as prod_cost,

  eval(  
    'select '||c.price_expression||' from product where id=:pid',
    '{"{cost}",:pid}',  
    array[ p.cost, p.id ]  
  )      as cust_cost

from product p,  customer c

But of course it may be slow, insecure, you could use materialized views to cache it more easily, etc. - see docu there.

edited Apr 30, 2016 at 8:49

answered Apr 28, 2016 at 9:01

Andreas Covidiot

4,8335 gold badges55 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Andrus Over a year ago

Thank you. Great. I marked it as answer and upvoted.

Andrus Over a year ago

I tried but got error. I posted it in stackoverflow.com/questions/36939625/…

Andreas Covidiot Over a year ago

I updated my answer for your problem (I did not actually try it out myself with your example!) ... you just have to add all your parameters and tables necessary for your specific "dyna-sql" (product and :cid). better use :cost instead of {cost} in your data since it is more convenient, understandable and does not conflict with array syntax.

Andreas Covidiot Over a year ago

ahh ... can't edit last comment anymore :-/ ... must be :pid of course (instead :cid)

Andrus Over a year ago

You can delete comment and enter it again as new. Columns are added to customer and product tables at runtime and expressions are also created at runtime. How to pass all columns automatically to eval, without using hard-coded parameters? plpgsql has row type, maybe this can used

|

Craig Ringer · Accepted Answer · 2014-09-26 16:47:55Z

1

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.

answered Sep 26, 2014 at 16:47

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

2 Comments

Andrus Over a year ago

Based on accepted answer it may be doable. I posted realted question in stackoverflow.com/questions/36939625/…

Craig Ringer Over a year ago

Um, yes. You're using PL/PgSQ with EXECUTE. As I advised above.

Collectives™ on Stack Overflow

How to evaluate expression in select statement in Postgres

2 Answers 2

9 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related