如何评估Postgres中select语句中的表达式 [英] How to evaluate expression in select statement in Postgres

问题描述

Postgres 9.1+ 数据库包含客户和产品.在客户表中，客户价格被描述为每个客户的价格表达式列中的 sql 表达式.

Postgres 9.1+ database contains customers and product. In customers table, customer price is described as sql expression in priceexpression column for every customer.

如何根据这些数据创建价目表?我尝试了下面的代码，但由于 eval() 未定义而出错.

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

这是 ASP.NET MVC4 应用程序.

This is ASP.NET MVC4 application.

推荐答案

您可以编写一个 SQL 函数来为您执行此操作并使用例如postgres-utils 提供的那些:

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.

这篇关于如何评估Postgres中select语句中的表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！