For循环内部的PostgreSQL更新 [英] Postgresql Update inside For Loop

问题描述

我已经足够了解Postgresql了，但是在使用for循环更新表中的空值列时遇到了问题。我正在处理的表格非常庞大，为简洁起见，我将举一个较小的示例，该示例应能阐明这一点。取得下表

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

+----+----------+----------+
| 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    |
+----+----------+----------+

我想编写一个for循环，该循环遍历所有行，并对a和b列中的值进行一些操作
，然后在c中插入新值。
例如，其中id = a，更新表集合C = A * B或id = d集合C = A + B等。然后，这将给我一个类似

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

+----+----------+----------+
| 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       |
+----+----------+----------+ 

因此，最终我想遍历表的所有行，并根据 id列中的值更新C列。我编写的函数（既不给出任何错误，也没有更新任何内容）看起来像这样……

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...

-- 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.

推荐答案

不需要循环或函数，只需执行一次 update code>语句：

No need for a loop or a function, this can be done with a single update statement:

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

函数未执行的原因

update table set C = result 
WHERE id = '';

在id 列中没有空字符串的行。您的函数似乎也使用了错误的公式：当id ='a'则B * C 我想那应该是： then a * b 。由于 C 最初为 NULL ，因此 b * c 也将产生null。因此，即使 if 您在循环中的更新也会找到一行，也会将其更新为 NULL 。

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*C I guess that should be: then a*b. As C is NULL initially, b*c will also yield null. So even if your update in the loop would find a row, it would update it to 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.

或者换句话说：进行基于集合的操作（例如我的单个 update 示例）是永远是更好的选择。

Or to put it the other way round: doing set-based operations (like my single update example) is always the better choice.

这篇关于For循环内部的PostgreSQL更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！