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

increases the sequence by 10
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.

select nextval('an_iter') into val; can be simplified to val := nextval('an_iter'); (and that's a bit more efficient as well) — user330315
– user330315, Commented May 21, 2021 at 11:42

Jim Jones · Accepted Answer · 2021-05-21 11:46:29Z

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

edited May 21, 2021 at 11:46

answered May 21, 2021 at 11:35

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL generate results sequentially

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related