postgresql - plpgsql 循环查询和更新

Question

我有一个名为 test 的表，我按名为 daterange 的范围的上限对它进行排序。我想获取此排序数据的顺序并将其保存在名为 bc_order 的同一表的另一列中。

排序的顺序必须保存在bc_order列中。

所以我对我想要的数据进行排序，循环遍历结果，每次都增加一个计数器，然后用当前计数器更新 bc_order 列。

CREATE OR REPLACE FUNCTION __a_bc_order() RETURNS void  AS $$
    DECLARE
        iterator integer := 1;
        r  test%rowtype;
    BEGIN
        for r in 
        select * from test where upper(test.daterange)<0 order by upper(test.daterange) DESC
        loop     
            update test SET bc_order  = iterator where test.id = test.id ;    
            iterator := iterator + 1;
        end loop ;
    END
$$ LANGUAGE plpgsql;

select __a_bc_order();

我希望能得到类似的东西

id        daterange     bc_order
14  [-3001011,-2000101) 1
21  [-3001202,-3000102) 2
19  [-3010102,-3000102) 3
22  [-4001202,-4000102) 4
23  [-4000702,-4000302) 5
27  [-6000401,-6000201) 6
26  [-6000501,-6000301) 7

但是我明白了

id        daterange     bc_order
14  [-3001011,-2000101) 7
21  [-3001202,-3000102) 7
19  [-3010102,-3000102) 7
22  [-4001202,-4000102) 7
23  [-4000702,-4000302) 7
27  [-6000401,-6000201) 7
26  [-6000501,-6000301) 7

我想循环有一个我无法修复的问题。请指教。我使用 PostgreSQL 10

Answer 1

无需使用循环或 PL/pgSQL:

update test
  set bc_order = t.rn
from (
  select id, row_number() over (order by upper(daterange) desc) as rn
  from test
  upper(daterange) < 0 
) t
where t.id = test.id;