CODEWITHSUNDEEP
postgresql
H.W
H.W

Reputation: 15

How to return intermediate results in postgreSQL?

CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF int AS
$BODY$
DECLARE
    i int;
begin
    i=1;
    LOOP
        RETURN NEXT f(i);--assume f() cost one second to return
        i=i+1;
        EXIT when i>1000;
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

now if I call get_all_foo(), it will cost me 1000 sec to see the final result. However, I want the intermediate results f(i) to be visible, but how?

P.S. I expect the following output

at 1 sec: f(1)

at 2 sec: 2 rows

f(1)

f(2)

at 3 sec: 3 rows

f(1)

f(2)

f(3)

at 4 sec: 4 rows...

Thank you in advance :)

Upvotes: 1

Views: 182

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247545

PL/pgSQL caches the result rows in a tuplestore, so you have to wait until the function is done. There is probably a way to return rows immediately if you write the function in C.

Upvotes: 1

Related Questions