Re: “Loop” in plpgsql Function - PostgreSQL 9.2

"drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com> writes:

> So, the new plan is:
>
> 1 - Select 50.000 rows and gives it a batch number.
> 2 - Select *MORE* 50,000 rows and gives it a *NEW* batch number.
> 3 - Select *MORE* 50,000 rows and gives it a *NEW* batch number.

Why so complicated? Here's a simplified example:

CREATE TABLE mytable (
id serial NOT NULL,
payload int NOT NULL,
batch_number int NULL,
PRIMARY KEY (id)
);

INSERT INTO mytable (payload)
SELECT x FROM generate_series(1, 2000) AS g(x);

\set batchSize 600

UPDATE mytable
SET batch_number = (id % (SELECT count(*) FROM mytable) / :batchSize) + 1;

SELECT batch_number, count(*) AS cnt
FROM mytable
GROUP BY batch_number
ORDER BY batch_number;