Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2
Дата
Msg-id CAKFQuwaX6x+wdk_kX06pMcBE2k4QhwF_LL2ExgRM1P0pyH9sOg@mail.gmail.com
обсуждение исходный текст
Ответ на “Loop” in plpgsql Function - PostgreSQL 9.2  ("drum.lucas@gmail.com" <drum.lucas@gmail.com>)
Ответы Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2  ("drum.lucas@gmail.com" <drum.lucas@gmail.com>)
Список pgsql-general
Дерево обсуждения
On Mon, Jan 18, 2016 at 2:37 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:

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?

It is customary to restrict emails to a single list or at least make others aware when you do have a legitmate need to cross-post.

Specifically your post on -admin <“Break” in plpgsql Function - PostgreSQL 9.2>

Yes, it didn't belong on -admin in the first place but posting it here with a different title and not indicating on either thread that the other exists and/or is the valid one just makes it harder for others to follow along.


As for your general question I try to approach this problem in the following manner:

SELECT however many of something that you need (FOR UPDATE)
UPDATE those selected to indicate that they have been seen
PROCESS them as needed
​repeat step 1 until it returns no records

​It doesn't always work - and given a sufficiently large number of records it may be unadvisable - but it is set-oriented which is generally a plus in SQL.

The other way to assign batches is to use the integer modulus operator (e.g., 10 % 3 = 1 : read 3 remainder of 1) or integer division (10 / 3 = 3) to derive the batch number based upon an attribute of the data itself as opposed to its order of appearance in a result set.

David J.


​
 

В списке pgsql-general по дате отправления:

Предыдущее
От: "drum.lucas@gmail.com"
Дата:
Сообщение: “Loop” in plpgsql Function - PostgreSQL 9.2
Следующее
От: "drum.lucas@gmail.com"
Дата:
Сообщение: Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2