I've created a function that allows me to do an huge update.
But I need to limit this function. I need to do 50k rows (example) and then stop it. After that I need to continue from the rows that I've stopped... I'll have to modify the call function (select batch_number();) as well.
How can I do that? Using for?
Thank you.
Code:
CREATEor REPLACE FUNCTION batch_number()
RETURNS INTEGER AS$$declare batch_num integer; offset_num integer;begin offset_num =0; batch_num =1;while(select true from gorfs.nfs_data where batch_number isnull limit 1) loop with ids(id)as(select file_id from gorfs.nfs_data orderby file_id offset offset_num limit 1000)update gorfs.nfs_data set batch_number = batch_num from ids where file_id = ids.id;
offset_num = offset_num +1000; batch_num = batch_num +1;end loop;return batch_num;end$$ language 'plpgsql';