For 循环内的 Postgresql 更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25507477/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql Update inside For Loop
提问by tasslebear
I'm new enough to postgresql, and I'm having issues updating a column of null values in a table using a for loop. The table i'm working on is huge so for brevity i'll give a smaller example which should get the point across. Take the following table
我是 postgresql 的新手，我在使用 for 循环更新表中的空值列时遇到问题。我正在处理的表格很大，所以为了简洁起见，我将举一个较小的例子，它应该能说明问题。拿下表
+----+----------+----------+
| id | A | B | C |
+----+----------+----------+
| a | 1 | 0 | NULL |
| b | 1 | 1 | NULL |
| c | 2 | 4 | NULL |
| a | 3 | 2 | NULL |
| c | 2 | 3 | NULL |
| d | 4 | 2 | NULL |
+----+----------+----------+
I want to write a for loop which iterates through all of the rows and does some operation on the values in columns a and b and then inserts a new value in c. For example, where id = a , update table set C = A*B, or where id = d set C = A + B etc. This would then give me a table like
我想编写一个 for 循环，它遍历所有行并对 a 和 b 列中的值进行一些操作，然后在 c 中插入一个新值。例如， where id = a ，update table set C = A*B，或者 where id = d set C = A + B 等等。这会给我一个表格
+----+----------+----------+
| id | A | B | C |
+----+----------+----------+
| a | 1 | 0 | 0 |
| b | 1 | 1 | NULL |
| c | 2 | 4 | NULL |
| a | 3 | 2 | 6 |
| c | 2 | 3 | NULL |
| d | 4 | 2 | 6 |
+----+----------+----------+
So ultimately I'd like to loop through all the rows of the table and update column C according to the value in the "id" column. The function I've written (which isn't giving any errors but also isn't updating anything either) looks like this...
所以最终我想遍历表的所有行并根据“id”列中的值更新列 C。我写的函数（它没有给出任何错误，也没有更新任何东西）看起来像这样......
-- DROP FUNCTION some_function();
CREATE OR REPLACE FUNCTION some_function()
RETURNS void AS
$BODY$
DECLARE
--r integer; not too sure if this needs to be declared or not
result int;
BEGIN
FOR r IN select * from 'table_name'
LOOP
select(
case
when id = 'a' THEN B*C
when id = 'd' THEN B+C
end)
into result;
update table set C = result
WHERE id = '';
END LOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql
I'm sure there's something silly i'm missing, probably around what I'm, returning... void in this case. But as I only want to update existing rows should I need to return anything? There's probably easier ways of doing this than using a loop but I'd like to get it working using this method. If anyone could point me in the right direction or point out anything blatantly obvious that I'm doing wrong I'd much appreciate it. Thanks in advance.
我确定我遗漏了一些愚蠢的东西，可能是关于我的东西，返回......在这种情况下无效。但由于我只想更新现有行，我是否需要返回任何内容？这样做可能比使用循环更简单，但我想使用这种方法让它工作。如果有人能指出我正确的方向或指出任何明显我做错的事情，我将不胜感激。提前致谢。
回答by a_horse_with_no_name
No need for a loop or a function, this can be done with a single updatestatement:
不需要循环或函数，这可以用一条update语句完成：
update table_name
set c = case
when id = 'a' then a*b
when id = 'd' then a+b
else c -- don't change anything
end;
SQLFiddle: http://sqlfiddle.com/#!15/b65cb/2
SQLFiddle：http://sqlfiddle.com/#!15/b65cb/2
The reason your function isn't doing anything is this:
你的函数没有做任何事情的原因是：
update table set C = result
WHERE id = '';
You don't have a row with an empty string in the column id. Your function also seems to use the wrong formula: when id = 'a' THEN B*CI guess that should be: then a*b. As Cis NULLinitially, b*cwill also yield null. So even ifyour update in the loop would find a row, it would update it to NULL.
您在 column 中没有包含空字符串的行id。您的函数似乎也使用了错误的公式：when id = 'a' THEN B*C我想应该是：then a*b。由于C是NULL初期，b*c也会产生空。因此，即使当你在循环更新会发现一排，将更新它NULL。
You are also retrieving the values incorrectly from the cursor.
您还错误地从游标中检索了值。
If you really, really want to do it inefficiently in a loop, the your function should look something like this (not tested!):
如果你真的，真的想在循环中低效地执行它，你的函数应该看起来像这样（未经测试！）：
CREATE OR REPLACE FUNCTION some_function()
RETURNS void AS
$BODY$
DECLARE
result int;
BEGIN
-- r is a structure that contains an element for each column in the select list
FOR r IN select * from table_name
LOOP
if r.id = 'a' then
result := r.a * r.b;
end if;
if r.id = 'b' then
result := r.a + r.b;
end if;
update table
set C = result
WHERE id = r.id; -- note the where condition that uses the value from the record variable
END LOOP;
END
$BODY$
LANGUAGE plpgsql
But again: if your table is "huge" as you say, the loop is an extremely bad solution. Relational databases are made to deal with "sets" of data. Row-by-row processing is an anti-pattern that will almost always have bad performance.
但同样：如果你的桌子像你说的那样“巨大”，那么循环是一个非常糟糕的解决方案。关系数据库是用来处理“数据集”的。逐行处理是一种反模式，它几乎总是有很差的性能。
Or to put it the other way round: doing set-based operations (like my single updateexample) is always the better choice.
或者反过来说：进行基于集合的操作（如我的单个update示例）总是更好的选择。
