From: | "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | “Break” in plpgsql Function - PostgreSQL 9.2 |
Date: | 2016-01-18 20:38:06 |
Message-ID: | CAE_gQfVACaKT90VWKgrooFk+dTSr1Fa4SVzvVKYH9WJjU5kMvw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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:
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 | Williams, Alex | 2016-01-20 20:50:25 | postgres_fdw for postgres 9.2 |
Previous Message | Saulo Merlo | 2016-01-14 21:47:25 | Re: Query - Create PostgreSQL |