I am trying to write a migration for my customer table where the my new column new_number should get incremental values (starting from 10000 because of a business rule) for each company like the following:

I have written a query that does the trick by using a function that fetches the last inserted number and adds 1 to the new row. But is way too slow and causes timeout when run on thousands of rows.

create or replace function get_number (id_company uuid)
returns int
language plpgsql
as
$$
declare
new_number integer;
begin
select
    c.new_number
into new_number
from
   customer c
where
  c.company_id = id_company
  and c.new_number is not null
order by c.new_number desc
limit 1;
return new_number;
end;
$$;

update
   customer c
set
    new_number =
        case
            when get_number(c.company_id) is null then 1 else get_number(c.company_id)   1
            end
where c.new_number is null;

Is there a simpler way to achieve what I am trying to do?

Thank you in advance!

CodePudding user response：

You could avoid a UDF and instead use an update join with the help of ROW_NUMBER:

UPDATE customer AS c1 
SET new_number = c2.rn
FROM
(
    SELECT id, ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY id) rn
    FROM customer
) c2
WHERE c1.id = c2.id;

CodePudding user response：

if I understand correctly you can try to ROW_NUMBER window function instead of function.

WITH cte AS (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY company_id ORDER BY id) rn
    FROM customer 
)
UPDATE customer t1
SET new_number = cte.rn   9999
FROM cte
WHERE t1.new_number IS NULL AND t1.id = cte.id