postgresql - 使用许多常见的子表达式进行更新

Question

计算表的最佳方法是什么，其中许多列是根据同一表中的其他列计算的，并且公式以复杂的方式相互构建。

如果您不关心性能，您可以简单地计算每个列并使用其自己的更新语句:

update x set b1 = a1 + a2;
update x set b2 = a3 * b1;
update x set b3 = a4 * b2;
...

或者如果您手动扩展公式，您可以在一次更新中完成所有操作:

update x set
  b1 = a1 + a2,
  b2 = a3 * (a1 + a2),
  b3 = a4 * (a3 * (a1 + a2));

这里的问题是这些公式会变得庞大且难以更改和调试。

在 Postgres(或者甚至是一般的 SQL)中是否有某种方法可以让您在这些情况下兼顾性能和可维护性？

这是我希望我能做的事情:

update x set
  b1 = _b1,
  b2 = _b2,
  b3 = _b3
with
  _b1 = a1 + a2,
  _b2 = a3 * _b1,
  _b3 = a4 * _b2;

我有一个可行的解决方案，其中公式在具有多个返回值的函数中定义，但这在某些其他方面相当不灵活，因此我正在寻找替代方案。

Answer 1

您可以在最新的 PostgreSQL 版本中使用以下内容:

UPDATE atable
SET (col1, col2, col3) =
SELECT expr1, expr2, expr3
FROM ...

如果需要在查询中重用计算表达式，可以使用WITH:

WITH t1(x1) AS (
   SELECT /* complicated */
), t2(x2) AS (
   SELECT /* complicated using t1 */
) ...
SELECT /* final results */

我不确定这是否会使代码对您来说更具可读性，但它避免使用函数并且符合标准(我认为，没有检查)。