“Loop” in plpgsql Function - PostgreSQL 9.2
От | drum.lucas@gmail.com |
---|---|
Тема | “Loop” in plpgsql Function - PostgreSQL 9.2 |
Дата | |
Msg-id | CAE_gQfWui78P1C0ZBNdUSKS2Fn7eUdt3LpNH_CR5J-7VAg+xiw@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2
Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2 Re: “Loop” in plpgsql Function - PostgreSQL 9.2 |
Список | 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';
В списке pgsql-general по дате отправления: