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

我想编写一个循环遍历所有行并执行一些操作在 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 语句完成:

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

你的函数没有做任何事情的原因是:

The reason your function isn't doing anything is this:

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

id 列中没有包含空字符串的行.您的函数似乎也使用了错误的公式:when id = 'a' THEN 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屋！