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