PostgreSQL generate results sequentially

Question

I want to give out result from a PostgreSQL database sequentially like a python Generator. When the function get_data is called every time I want to return a new 10 rows from the rows.

I defined a sequence called an_iter which is increased by 10:

create sequence an_iter
    increment by 10;

My plan is to create a function that

1. increases the sequence by 10
2. return the rows which are bigger than sequence's current value (limit to 10)

My function definition:

CREATE OR REPLACE FUNCTION public.get_data()
    RETURNS SETOF ppdb
    LANGUAGE plpgsql AS
$func$
declare
    val integer := 0;
BEGIN

    select nextval('an_iter') into val;
    Return Query SELECT * from ppdb where i<= val+10 limit 10;
END
$func$; 

Though I called the function the same result set is returned.

Answer 1

Have you tried using BETWEEN? Doing so you can get rid of the LIMIT 10, e.g.

CREATE OR REPLACE FUNCTION public.get_data()
    RETURNS SETOF ppdb
    LANGUAGE plpgsql AS
$func$
declare
    val integer := 0;
BEGIN

    SELECT nextval('an_iter') INTO val;
    RETURN QUERY SELECT * FROM ppdb 
                 WHERE i BETWEEN val AND val+10;
END
$func$; 

Demo: db<>fiddle