sql - POSTGRES - 将(子)表达式错误评估为 NULL

Question

我在 Postgres 表 measurements 中有传感器数据，其中包含列 id、timestamp、s0、s1, s2, ...

此外，列(id, timestamp) 上有一个索引。我想允许使用动态数学表达式(在下面的示例中:sin(s3)*0.1000/s5)来计算派生值。

SELECT
  timestamp,
  trunc((sin(s3) * 0.1000/s5)::numeric, 3) AS "calculated"
FROM measurements
WHERE id = 42
ORDER BY timestamp DESC
LIMIT 10000;

显然，这很容易出现“被零除”错误，这将使查询失败。有没有办法捕获此错误并返回例如NULL 为会发生错误的计算值？

灵感来自

• Postgres return null values on function error/failure when casting
• Store a formula in a table and use the formula in a function

我已经尝试定义一个 postgres 函数 eval_numeric(sensors int[], formula text) 解析公式并在异常时返回 NULL .上面 SQL 语句的第三行现在是

trunc(eval_numeric(ARRAY[s3,s5],'sin(var1)*0.1/var2'), 3) AS "calculated"

这提供了所需的行为，但执行时间如 EXPLAIN ANALYZE 所报告的那样增加了 20 倍(~20ms -> ~400ms)。还有其他想法吗？

---

更新

要评估的动态表达式源于网络应用程序用户。所以上面的公式只是一个例子(可能需要检查平方根的负参数)。我宁愿有一个通用的错误检查可能性，也不希望在数学表达式中有逻辑。这对最终用户来说会更容易，我可以验证允许的数学，例如使用数学解析器，从而防止 SQL 注入(inject)。

Answer 1

你能把表达式改成这样吗？

SELECT timestamp,
       trunc((sin(s3) * 0.1000/nullif(s5, 0))::numeric, 3) AS "calculated",
FROM measurements
WHERE id = 42
ORDER BY timestamp DESC
LIMIT 10000;

这是完成您想做的事情的最简单方法。