Stream every row coming from a Postgres function

Question

This is the how the function definition looks:

CREATE OR REPLACE FUNCTION my_function(foo NUMERIC) 
RETURNS SETOF my_type AS $$
DECLARE rec record;
BEGIN   
    FOR rec IN SELECT * FROM my_table WHERE my_table.bar > foo
    LOOP
        RETURN QUERY SELECT 
            my_scalar_function(rec.bar1),
            my_scalar_function(rec.bar2),
            my_scalar_function(rec.bar3);
    END LOOP;                                             
END;
$$ LANGUAGE plpgsql;

On the client i am trying to use a QueryStream from the pg-query-stream package:

pool.connect((err, client, done) => {
  if (err) throw err
  const query = new QueryStream('SELECT * FROM my_function($1);', [123])
  const stream = client.query(query)
  stream.on('end', ()=>{
    console.log('finished')
    done()
    process.exit(0)
  })
  stream.pipe(JSONStream.stringify()).pipe(process.stdout)
});

But still i am getting all rows at the end of the function and no intermediate rows. I would like to stream every row when it is calculated in the my_function.

Answer 1

Q: But still i am getting all rows at the end of the function and no intermediate rows

Unfortunately these results are accumulated (as opposed to streamed) by the server-side implementation of RETURN QUERY. This is explicitly mentioned in the documentation:

Note
The current implementation of RETURN NEXT and RETURN QUERY stores the entire result set before returning from the function, as discussed above. That means that if a PL/pgSQL function produces a very large result set, performance might be poor: data will be written to disk to avoid memory exhaustion, but the function itself will not return until the entire result set has been generated. A future version of PL/pgSQL might allow users to define set-returning functions that do not have this limitation.