CODEWITHSUNDEEP
postgresqlloopssortingsql-updateplpgsql
codebot
codebot

Reputation: 707

plpgsql loop through query and update

I have a table called test and I sort it by the upper range of a range called daterange. I want to take the order of this sorted data and save it in another column of the same table named bc_order.

The order of the sort must be saved in the bc_order column.

So I order the data I want, I loop through the results and I increase a counter every time, then update the bc_order column with the current counter.

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();

I was hoping can get something like

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

but I get

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

I guess there is an issue with the looping that I cannot fix. Please advice. I use PostgreSQL 10

Upvotes: 0

Views: 3646

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45910

Your code is wrong.

update test SET bc_order  = iterator where test.id = test.id ;

it is effectively 

update test SET bc_order  = iterator where true;

and it is exactly your result.

should be

update test SET bc_order  = iterator where test.id = r.id;

@a_horse_with_no_name helps well - what is possible with readable one SQL statement, should be done by one SQL query.

Upvotes: 2

user330315
user330315

Reputation:

No need to use a loop or 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;

Upvotes: 2

Related Questions