Looping through relational table

Question

Is it possible to loop through postgres while changing the values of Limit and offset. I want to loop through till the end of the relational table is reached. The reason I want to dynamically set Limit and offset is that I have very little virtual memory of 1 GB due to which my process is getting Killed.

Currently the approach I am taking is to manually set the values of limit and offset.

select *  from my_relational_table limit 1000 offset 0;
select *  from my_relational_table limit 1000 offset 1000;
select *  from my_relational_table limit 1000 offset 2000;

Is it possible to automate the process within postgres such that the loop stops when the end of the relational table is reached.

Answer 1

Yes, It is possible. You can use a cursor and command FETCH.

• http://www.postgresql.org/docs/current/static/sql-declare.html
• http://www.postgresql.org/docs/current/static/sql-fetch.html

Example:

postgres=# begin read only;
BEGIN
postgres=# declare xx cursor for select * from generate_series(1,100);
DECLARE CURSOR
postgres=# fetch 2 xx;
 generate_series 
-----------------
               1
               2
(2 rows)

postgres=# fetch 2 xx;
 generate_series 
-----------------
               3
               4
(2 rows)

postgres=# fetch 2 xx;
 generate_series 
-----------------
               5
               6
(2 rows)

postgres=# fetch 2 xx;
 generate_series 
-----------------
               7
               8
(2 rows)

postgres=# commit;
COMMIT