如何用PL/pgSQL遍历查询结果并更新test表的bc_order排序列

嘿，你已经找对方向了！我帮你把这个函数补全，同时给你一个更高效的替代方案——毕竟逐行遍历在数据量大的时候可能有点拖慢速度。

首先，按照你一开始的思路，用游标遍历的完整函数如下：

CREATE OR REPLACE FUNCTION __a_bc_order() RETURNS void AS $$
DECLARE
    iterator integer := 1;
    -- 定义游标：按daterange字段的上界排序，这里假设表有主键id用来定位行
    cur_test CURSOR FOR 
        SELECT id 
        FROM test 
        ORDER BY upper(daterange_field) ASC; -- 要降序就改成DESC
    current_id integer;
BEGIN
    -- 可选操作：先清空旧的bc_order值，避免残留数据干扰
    UPDATE test SET bc_order = NULL;

    -- 开启游标遍历
    OPEN cur_test;
    LOOP
        -- 取出当前行的主键id
        FETCH cur_test INTO current_id;
        -- 没有更多数据就退出循环
        EXIT WHEN NOT FOUND;

        -- 更新当前行的排序序号
        UPDATE test 
        SET bc_order = iterator 
        WHERE id = current_id;

        -- 计数器自增
        iterator := iterator + 1;
    END LOOP;
    -- 关闭游标
    CLOSE cur_test;
END;
$$ LANGUAGE plpgsql;

关键细节说明： #

• 我用了upper(daterange_field)来获取daterange的上界，这是PostgreSQL处理范围类型的标准函数，如果你用的是时间范围（比如tsrange），用法完全一样。
• 这里假设你的表有主键id，如果你的唯一标识列是其他名字（比如uuid或者自定义的唯一键），记得把id替换成对应的列名，不然没法精准定位要更新的行。
• 开头的重置操作是可选的，如果你的bc_order之前是空的，或者你想覆盖旧值，加上它更稳妥。

更高效的替代方案：用窗口函数批量更新 #

如果你的表数据量不小，逐行遍历的效率会比较低，PostgreSQL支持用窗口函数一次性完成更新，不需要写函数：

UPDATE test
SET bc_order = sub.rn
FROM (
    SELECT 
        id,
        ROW_NUMBER() OVER (ORDER BY upper(daterange_field) ASC) AS rn
    FROM test
) AS sub
WHERE test.id = sub.id;

这个写法直接通过子查询生成每行的排序序号，然后批量更新，性能比游标遍历好很多，而且代码更简洁——如果你不需要重复执行这个逻辑，直接用这条SQL就够了。

注意点： #

• 如果需要降序排序，把两个例子里的ASC改成DESC就行。
• 确保daterange_field确实是PostgreSQL的daterange类型，不然upper()函数会报错。

内容的提问来源于stack exchange，提问作者codebot