“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  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2  (dinesh kumar <dineshkumar02@gmail.com>)
Re: “Loop” in plpgsql Function - PostgreSQL 9.2  (mariusz <marius@mtvk.pl>)
Список 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 по дате отправления:

Предыдущее
От: Stéphane Schildknecht
Дата:
Сообщение: Re: CoC [Final]
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2