如何在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屋！