plpgsql-update-in-batch

plpgsql-update-in-batch.sql

DO $$DECLARE quantity integer := 1; maxid integer; cnt integer; total integer := 0;
  BEGIN
    SELECT max(id) INTO maxid FROM google_places;
    maxid := maxid + 10;
    RAISE NOTICE 'maxid: %', maxid;
    LOOP
      UPDATE some_table t1
      SET some_column = ...
      FROM another_table t2
      WHERE ...
        AND ...
        AND t1.id BETWEEN quantity AND quantity + 999;
      GET DIAGNOSTICS cnt = ROW_COUNT;
      -- RAISE NOTICE '[%] updated %', quantity, cnt;
      total := total + cnt;
      quantity := quantity + 1000;
      IF quantity > maxid THEN
          EXIT;  -- exit loop
      END IF;
    END LOOP;
    RAISE NOTICE 'Done! Total updated % rows', total;
  END;
  $$ LANGUAGE plpgsql;