1

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.

Andrus
  • 26,339
  • 60
  • 204
  • 378

2 Answers2

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Based on accepted answer it may be doable. I posted realted question in http://stackoverflow.com/questions/36939625/how-to-fix-eval-execution-error – Andrus Apr 29 '16 at 13:28
  • Um, yes. You're using PL/PgSQ with `EXECUTE`. As I advised above. – Craig Ringer Apr 29 '16 at 16:12
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.

Andreas Covidiot
  • 4,286
  • 5
  • 51
  • 96
  • Thank you. Great. I marked it as answer and upvoted. – Andrus Apr 29 '16 at 13:10
  • I tried but got error. I posted it in http://stackoverflow.com/questions/36939625/how-to-fix-eval-execution-error – Andrus Apr 29 '16 at 13:27
  • 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 Apr 30 '16 at 08:44
  • ahh ... can't edit last comment anymore :-/ ... must be `:pid` of course (instead `:cid`) – Andreas Covidiot Apr 30 '16 at 08:51
  • 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 – Andrus Apr 30 '16 at 19:11
  • (Thx for the comment deletion hint :) - next time I'll remember, for now I'll leave it.) You could write a custom *eval(...)* function that takes all the row types you need as params to be able to use them dynamically. `eval( 'select ...', p_product_row := p, ... )`. For that the type must be explicitely defined, e.g. `create type product ...`. But if a user can really add/remove/alter columns himself (e.g. in a *MS SharePoint* like scenario), you can safely only add columns(!) then without having to drop the function etc.. Please ask a new quest. if you want to know more details about it. – Andreas Covidiot May 01 '16 at 01:20
  • Product type is probably created by default since table exists, no need to re-create?. Or is it better to pass customer and product ids as parameters to eval? Eval can add from and where clauses then. – Andrus May 01 '16 at 09:45
  • *Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type.* ... I was not aware of this before. So it should be easy then. http://www.postgresql.org/docs/current/static/rowtypes.html#AEN7991. the ids you pass in the 3rd param `eval( 'select ...' , ..., )` section of my above comment. – Andreas Covidiot May 01 '16 at 11:16
  • Former question was long time ago without your answer. Hard-coded expressons in case statement are currently used. This will not work in some customer sites where specific expressions are used. In next time when application is re-factored we can consider using your answer. – Andrus May 01 '16 at 13:32