Fetch multiple Refcursor in node js from postgres db (using pg-promise)

Question

I need to fetch multiple cursors from pg functions in Nodejs.

I have tried both kinds of function writing methods in PG as said in below link: http://www.sqlines.com/postgresql/how-to/return_result_set_from_stored_procedure

In PG Admin||| query tool i can able to view the output for multiple cursors by giving the name of it. I want to fetch the same from node js application, I'm using "pg-promise".

Option-1

 db.func('get_data',
    [
        name,
        key
    ])
    .then(dbResult => {
        console.log(" RES 1" + dbResult);               
    }).catch(error => {
        console.log(error);
        throw error;
    });

Option-2

var retCursor1 = {};
var retCursor2 = {};
db.func('get_data',
    [
        name,
        key,
        retCursor1,
        retCursor2,
    ])
    .then(dbResult => {
        console.log(" RES 1" + dbResult);               
        console.log(" RES 2" + retCursor1);               
        console.log(" RES 3" + retCursor2);               
    }).catch(error => {
        console.log(error);
        throw error;
    });

get_data PG Fucntion will return 2 refCursors.

But No luck, can someone suggest what is the best way to fetch multiple cursor in Node js.

Answer 1

From the author of pg-promise.

---

Unfortunately, the underlying driver node-postgres does not support FETCH queries.

They recommend to use a separate module for it - node-pg-cursor, which I tried, but couldn't make it work for your case, see the issue I opened: https://github.com/brianc/node-pg-cursor/issues/34

My advise is to try and avoid it altogether, by not returning any cursors from functions, or at least the ones that you want to call directly from Node.js.

If you think in terms of performance and efficiency, then the best way is to execute your multiple SELECT queries in a single command via method multi, which was added in pg-promise v7.0.0:

db.multi('SELECT * FROM users WHERE name LIKE ${user.name};' +
    'SELECT * FROM products WHERE price BETWEEN ${price.min} AND ${price.max}', {
    user: {name: 'John'},
    price: {min: 1, max: 5}
})
    .then(data => {
        // data[0] = users
        // data[1] = products
    })
    .catch(error => {
        
    });

It will be just as fast as your function with two cursors, and way simpler.

Answer 2

db
.tx(t => {
  return t.batch([
            t.func('get_data', [name, key, retCursor1, retCursor2]),
            t.any('FETCH ALL IN $1', retCursor1),
            t.any('FETCH ALL IN $1', retCursor2)
        ]);
    })
.then(data => {
  console.log('DATA:', data); 
})
.catch(error => {
  console.log('ERROR:', error); 
});