0

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.

1
  • If the javascript driver buffers the complete result then where is the benefit of a stream? The repository shows an example with the generate_series function: const query = new QueryStream('SELECT * FROM generate_series(0, $1) num', [1000000]) May 12, 2021 at 10:07

1 Answer 1

3

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.

2
  • 1
    Great :) so no streaming. May 12, 2021 at 12:28
  • Ah damn :( I just had a great use-case pop into my head even if it did block one thread/process and require some adjustments to timeouts Feb 10, 2023 at 4:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.