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