Hello, On Mon, Jan 18, 2016 at 1:37 PM, drum.lu...@gmail.com <drum.lu...@gmail.com> wrote:
> 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? > > The function below is already working, but, the table has 40m rows. And > it's taking years. > > Do you need to run the function on any Staging(Not Production). I mean, do you want to run this batch processes on a single transaction. If not, I had the similar problem, where I needed to implement a function, which we can run in multiple sessions. I ran this function in one of the BI servers, where we have around 5 Million records. Find this <http://manojadinesh.blogspot.com/2015/07/parallel-operations-with-plpgsql_9.html>link about the implementation details. If your question was about "Using Loops", then please ignore my comments. FUNCTION: > > CREATE or 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 is null limit > 1) loop > with ids(id) as > ( > select > file_id > from > gorfs.nfs_data > order by > 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'; > > -- Regards, Dinesh manojadinesh.blogspot.com
