From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2 |
Date: | 2016-01-18 22:29:31 |
Message-ID: | CAKFQuwaX6x+wdk_kX06pMcBE2k4QhwF_LL2ExgRM1P0pyH9sOg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Jan 18, 2016 at 2:37 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | drum.lucas@gmail.com | 2016-01-18 22:34:33 | Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2 |
Previous Message | drum.lucas@gmail.com | 2016-01-18 21:37:41 | “Loop” in plpgsql Function - PostgreSQL 9.2 |