1

I'm looking for a way to evaluate price expressions stored in database in Postgres 9.1+

I tried code below from answer in How to evaluate expression in select statement in Postgres

but got error

ERROR:  missing FROM-clause entry for table "product"
LINE 1: select product.price*0.95

how to fix ?

Maybe it is possible to pass customer and product current row as eval parameters and to use them in eval expresion ?

create or replace function eval( sql  text ) returns text as $$
declare
  as_txt  text;
begin
  execute 'select ' ||   sql  into  as_txt ;
  return  as_txt ;
end;
$$ language plpgsql;


create table customer
( id int primary key,
  priceexpression text );
insert into customer values (1, 'product.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
Community
  • 1
  • 1
Andrus
  • 26,339
  • 60
  • 204
  • 378

2 Answers2

1

Serg is basically right. Your dyna-SQL is executed "on its own" so it needs to be a valid SQL statement (having "to know" all involved tables). I updated my answer in the referred thread to reflect this.

But to properly cite it in here your example should be something like (actually you should use the 2nd eval( sql text, keys text[], vals text[] ) variant!):

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

This should be more straight forward, robust and modular than Sergs suggestions.

Community
  • 1
  • 1
Andreas Covidiot
  • 4,286
  • 5
  • 51
  • 96
  • Answer contains hard-coded columns. Customers can add new columns to customer and product tables at runtime and create expressions at runtime which use those columns. How to use it in this case? – Andrus Apr 30 '16 at 19:17
  • @Andrus: please see answer here: http://stackoverflow.com/questions/26063328/how-to-evaluate-expression-in-select-statement-in-postgres/36909941?noredirect=1#comment61480828_36909941 – Andreas Covidiot May 01 '16 at 01:26
0

Just add the table name somwhere. May be 

insert into customer values (1, 'product.price*0.95 FROM product'),(2,'cost+12.0 FROM product' );

or may be 

 execute 'select ' ||   sql || ' FROM product' into  as_txt ;

at your choice.

Hope this priceexpression is not exposed to users but only to restricted number of admins, because it's dangerous sql injection security hole.

Serg
  • 22,285
  • 5
  • 21
  • 48
  • I think that will just end up evaluating the expression for every row of the table, where I think the intention is to evaluate it for the specific row of that product. – IMSoP Apr 29 '16 at 14:22
  • @IMSoP Maybe it is possible to pass customer and product current rows as eval parameters and to use them in eval expression ? I updated question – Andrus Apr 29 '16 at 14:28
  • @Serg only current row needs evaluated. Should where clause also added ? – Andrus Apr 29 '16 at 17:52
  • You need extra parameter in eval. Kind of eval(expr, pId) – Serg Apr 29 '16 at 20:14
  • @Serg. Customer and Product table columns are created in product sites and are not know at design time. Expressions may use all columns from those tables. How to pass all customer and product table columns to eval() as parameters or by some other way ? – Andrus Apr 30 '16 at 06:33