“Break” in plpgsql Function - PostgreSQL 9.2

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

Browse pgsql-admin by date

  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