本文介绍了对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()$ c
解决方案
您是否尝试过Postgres '非标准 UPDATE .. FROM
子句?我想象,这将工作
更新mytable
$ p注意,我要强制
设置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 $ c>自动加入
mytable
和gen
$ c>列。
请参阅此处的文档:
如果这似乎太复杂(即不是很可读),你仍然可以求助于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 ofmytable
.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 If that seems too complicated (i.e. not very readable), you can still resort to pgplsql as user Florin suggested here.这篇关于对Postgres 8.4中的每一行调用一次函数更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!