Re: “Loop” in plpgsql Function -PostgreSQL 9.2 - Mailing list pgsql-general

"drum.lucas@gmail.com" <drum.lucas@gmail.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;