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

问题描述

我有以下UPDATE语句：

  update mytable 
 set a = first_part（genid 
b = second_part（genid（）），
c = third_path（genid（））
其中package_id = 10; 
 

在此示例中，函数 genid（）每行调用三次，这是错误的 - 我希望每次 mytable 只调用一次。

我使用PostgreSQL 8.4数据库。如何写正确的更新？

我试过这样：

  update mytable 
 set a = first_part（g），
b = second_part（g），
c = third_path（g）
其中package_id = 10 
 genid（）as g; 
  

但它没有工作，因为 genid（）

解决方案

您是否尝试过Postgres '非标准 UPDATE .. FROM 子句？我想象，这将工作

 更新mytable 
设置a = first_part（gen.id），
b = new_part（gen.id），
c = third_path（gen.id）
从（
选择genid（）作为genid，id 
从mytable 
其中package_id = 10 
）gen 
其中mytable.id = gen.id; 
 --and package_id = 10  - 不再需要此谓词作为子查询
  - 已经过滤package_id，因为Erwin提到
   genid（）在中的每个记录中调用一次。<$ c> mytable 。然后我使用假设的 id 自动加入 mytable 和 gen  $ c>列。 
请参阅此处的文档：
 
 
 
  http://www.postgresql.org/docs/current/interactive/sql-update.html  
 
 
 
 这似乎只是与Postgres 9.0介绍。如果这似乎太复杂（即不是很可读），你仍然可以求助于pgplsql用户在这里推荐弗洛林。
 
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;
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.

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

I've tried something like this:
update mytable
   set a = first_part(g),
       b = second_part(g),
       c = third_path(g)
 where package_id = 10
  from genid() as g;
But it didn't work, because genid() has been called only once for the whole update statement.
解决方案
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
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

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屋！