Postgres function to devide-and-conquer (iterate) your big query

Create a postgres function to devide-and-conquer (iterate) your big query


TRUNCATE TABLE YOUR_NEW_BIG_TABLE;

DROP FUNCTION IF EXISTS pg_iterator();

CREATE OR REPLACE FUNCTION pg_iterator()
RETURNS void AS
$BODY$
DECLARE
vOffsetRecord INT;
vTotal INT;
vLimit INT;
vOffset INT;
BEGIN

-- How many?
-- select count(*) INTO currentRecord from YOUR_BIG_TABLE; -- 346,879,848
vTotal := 346879848;
-- vTotal := 5000;
vLimit := 100000;
vOffset :=0;

RAISE NOTICE 'DEBUG vTotal : %', vTotal;
RAISE NOTICE 'DEBUG vLimit : %', vLimit;
RAISE NOTICE 'DEBUG vOffset: %', vOffset;

LOOP
IF (vOffset >= vTotal) THEN
EXIT;
END IF;
EXECUTE format('INSERT INTO YOUR_NEW_BIG_TABLE (id, language, hash) SELECT id, language, hash FROM YOUR_BIG_TABLE ORDER BY id LIMIT %s OFFSET %s;', vLimit, vOffset);
RAISE NOTICE 'DEBUG vOffset: %', vOffset;
vOffset := vOffset + vLimit;
END LOOP;

END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION pg_iterator()
OWNER TO postgres;

select pg_iterator();

Leave a Reply

Your email address will not be published. Required fields are marked *


six × 1 =