在 Postgres 8.4 中为每一行调用一次的函数进行更新 [英] Update with function called once for each row in Postgres 8.4

问题描述

我有以下 UPDATE 语句:

I have the following UPDATE statement:

update mytable
   set a = first_part(genid()),
       b = second_part(genid()),
       c = third_path(genid())
 where package_id = 10;

在这个例子中，函数 genid() 每行被调用 3 次，这是错误的 - 我希望 mytable 的每一行只调用一次.

In this example the function genid() is called three times for each row, which is wrong - I want it to be called only once for each row of mytable.

我正在使用 PostgreSQL 8.4 数据库.如何编写正确的更新?

I'm using PostgreSQL 8.4 database. How to write the correct update?

我尝试过这样的事情:

update mytable
   set a = first_part(g),
       b = second_part(g),
       c = third_path(g)
 where package_id = 10
  from genid() as g;

但它不起作用，因为对于整个更新语句，genid() 只被调用了一次.

But it didn't work, because genid() has been called only once for the whole update statement.

推荐答案

你试过 Postgres 的非标准 UPDATE .. FROM 子句吗?我想，这会工作

Have you tried Postgres' non-standard UPDATE .. FROM clause? I imagine, this would work

update mytable
   set a = first_part(gen.id),
       b = second_part(gen.id),
       c = third_path(gen.id)
  from (
          select genid() as genid, id
          from mytable 
          where package_id = 10
       ) gen
 where mytable.id = gen.id;
 --and package_id = 10 -- This predicate is no longer necessary as the subquery
                       -- already filters on package_id, as Erwin mentioned

请注意，我强制 genid() 在子选择中的 mytable 中的每个记录只调用一次.然后我使用假设的 id 列自加入 mytable 和 gen.请参阅此处的文档:

Note that I'm forcing genid() to be called exactly once per record in mytable within the subselect. Then I'm self-joining mytable and gen using a hypothetical id column. See the documentation here:

http://www.postgresql.org/docs/current/interactive/sql-update.html

这似乎是仅在 Postgres 9.0 中引入的. 如果这看起来太复杂(即不太可读)，您仍然可以以用户身份使用 pgplsql Florin 建议在这里.

This seems to have been introduced with Postgres 9.0 only, though. If that seems too complicated (i.e. not very readable), you can still resort to pgplsql as user Florin suggested here.

这篇关于在 Postgres 8.4 中为每一行调用一次的函数进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！