如何修复postgres-utils eval()错误:缺少表"foo"的FROM子句条目? [英] How to fix postgres-utils eval() error: missing FROM-clause entry for table "foo"?

问题描述

我正在寻找一种评估Postgres 9.1+数据库中存储的价格表达方式的方法

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

我从答案中尝试了以下代码 如何在Postgres中的select语句中评估表达式

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

但出现错误

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

如何解决?

也许可以将客户和产品当前行作为评估参数，并在评估表达式中使用它们?

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 基本上是正确的. 您的 dyna-SQL 是自动执行的" ，因此它必须是有效的SQL语句(必须"知道所有涉及的表 ).我在引用的线程中更新了我的答案以反映这一点.

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.

但是要在此处正确引用，您的示例应类似于(实际上，您应该使用第二个eval( sql text, keys text[], vals text[] )变体！):

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

这应该比 Serg 的建议更直接，更可靠和更模块化.

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

这篇关于如何修复postgres-utils eval()错误:缺少表"foo"的FROM子句条目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！