I am doing a SELECT * FROM... with LIMIT query inside of a LOOP in my pgplsql function/procedure - something like this, e.g.
$$ DECLARE BEGIN LOOP exit when get diagnostics n_rec_count = 0; WITH cte_table AS ( SELECT * FROM my_table t1 INNER JOIN another_table t2 ON t1.id = t2.id LIMIT 10000 ); -- take the batch of 10,000 records from above -- and check if any IDs exist in another table DELETE FROM some_other_table t WHERE t.id IN (SELECT * FROM cte_table); get diagnostics n_rec_count = row_count; END LOOP; END; $$;
As you can see, the CTE expression
WITH cte_table AS ( SELECT * FROM my_table t1 INNER JOIN another_table t2 ON t1.id = t2.id LIMIT 10000 );
...is returning a batch of 10,000 records from the total amount of records, which is 169,246. Then, immediate after, I check if any ID fields in this
cte_table are in
some_other_table, and if they are, then I delete them, e.g.
DELETE FROM some_other_table t WHERE t.id IN ( SELECT * FROM cte_table );
This works fine on the 1st iteration because the
get diagnostics n_rec_count = row_count; will return a
n_rec_count == 10,000 because 10,000 records were just deleted.
However, the problem is on the 2nd iteration, the CTE that has the SELECT... LIMIT 10000 will just return the same 10000 records. All these records were already deleted, so
n_rec_count == 0 and the LOOP exits. This is not what I want at all, because I have 169,246 - 10,000 = 159,246 records left that I need to delete.
How can iterate forward or "grab" the next set of 10000 records using LIMIT? Or do I need a different LOOP or to use a CURSOR? All the examples I see with a LOOP or CURSOR involve iterating thru individual (1 record) at a time and that is NOT WHAT I WANTED. (or is this ok?)