/*
  Script original de Lucas e Rafael Fialho proveniente da
  discussao em https://listas.postgresql.org.br/pipermail/pgbr-geral/2016-January/042267.html

  Reproduzido aqui para fins de histórico a partir do conteudo do link:
  - https://drive.google.com/folderview?id=0B9VRju-W-e4nOHVzMExWcG5WakE&usp=sharing

  USE-O SOB SUA PRÓPRIA CONTA E RISCO
*/

CREATE SEQUENCE sq_batch_number
  INCREMENT 1
  MINVALUE 0
  MAXVALUE 922337203685477580;

CREATE or REPLACE FUNCTION batch_number(rows integer)
RETURNS INTEGER AS $$

declare
   batch_num integer;
   offset_num integer;
   crtRow record;
begin
    offset_num = 100000;
    SELECT nextval('sq_batch_number') into batch_num;
    
      ­­FOR i IN 1 .. rows loop
      FOR  crtRow in execute 'select file_id from table_name1 WHERE batch_number IS NULL limit '|| $1 ||' offset '|| $1*batch_num

      LOOP
        update
            table_name1
        set
            batch_number = batch_num
        where file_id = crtRow.file_id;
    end loop;
    
    return batch_num;
end

$$ language 'plpgsql';
