How to evaluate expression in select statement in

问题:

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.

回答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.

回答2:

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.