“Loop” in plpgsql Function - PostgreSQL 9.2

From: "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: “Loop” in plpgsql Function - PostgreSQL 9.2
Date: 2016-01-18 21:37:41
Message-ID: CAE_gQfWui78P1C0ZBNdUSKS2Fn7eUdt3LpNH_CR5J-7VAg+xiw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

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';

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2016-01-18 22:29:31 Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2
Previous Message Stéphane Schildknecht 2016-01-18 20:33:14 Re: CoC [Final]