sql - 遍历表，对每一行执行计算

Question

首先我想说我对 SQL 非常陌生，但我现在的工作要求我在其中工作。

我有一个包含地形点数据 (x,y,z) 的数据集。我正在尝试基于此数据构建 KNN 模型。对于每个点“P”，我在数据集中搜索最接近 P 的 100 个点(最近的意思是地理上最近)。然后，我对这些点的值进行平均(此平均值称为残差)，并将此值添加到表中的“残差”列中。

作为概念证明，我尝试简单地遍历表，并将每一行中“resid”列的值设置为 1.0。

我的查询是这样的:

CREATE OR REPLACE FUNCTION LoopThroughTable() RETURNS VOID AS '
DECLARE row table%rowtype;
BEGIN
    FOR row in SELECT * FROM table LOOP
        SET row.resid = 1.0;
    END LOOP;
END

' LANGUAGE 'plpgsql';

SELECT LoopThroughTable() as output; 

此代码执行并成功返回，但当我检查表时，没有进行任何更改。我的错误是什么？

Answer 1

在循环中逐行更新几乎总是一个坏主意，而且会非常慢并且无法扩展。你真的应该找到一种方法来避免这种情况。

说完之后:

您的函数所做的只是更改内存中列值的值 - 您只是在修改变量的内容。如果你想更新你需要一个update语句的数据:

您需要在循环内使用 UPDATE:

CREATE OR REPLACE FUNCTION LoopThroughTable() 
  RETURNS VOID 
AS
$$
DECLARE 
   t_row the_table%rowtype;
BEGIN
    FOR t_row in SELECT * FROM the_table LOOP
        update the_table
            set resid = 1.0
        where pk_column = t_row.pk_column; --<<< !!! important !!!
    END LOOP;
END;
$$ 
LANGUAGE plpgsql;

请注意，您必须在 update 语句的主键上添加 where 条件，否则您将更新 all 循环的每个 迭代的行。

一个稍微更有效的解决方案是使用游标，然后使用where current of

进行更新

CREATE OR REPLACE FUNCTION LoopThroughTable() 
  RETURNS VOID 
AS $$
DECLARE 
   t_curs cursor for 
      select * from the_table;
   t_row the_table%rowtype;
BEGIN
    FOR t_row in t_curs LOOP
        update the_table
            set resid = 1.0
        where current of t_curs;
    END LOOP;
END;
$$ 
LANGUAGE plpgsql;

---

So if I execute the UPDATE query after the loop has finished, will that commit the changes to the table?

没有。对该函数的调用在调用事务的上下文中运行。因此，如果您在 SQL 客户端中禁用了自动提交，则需要在运行 SELECT LoopThroughTable() 之后提交。

---

请注意，语言名称是一个标识符，不要在其周围使用单引号。您还应该避免使用像 row 这样的关键字作为变量名。

使用 dollar quoting (就像我所做的那样)也使编写函数体更容易