Andrus Andrus - 2 years ago 95
SQL Question

How to fix postgres-utils eval() error: missing FROM-clause entry for table "foo"?

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 $$
as_txt text;
execute 'select ' || sql into as_txt ;
return as_txt ;
$$ 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 as customer, as product,
eval(priceexpression) as price
from customer,product

Answer Source

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!):

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download