1

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.

Dinesh
  • 51
  • 1
  • 6
  • What you are passing into `db.func` as parameters can only be input parameters. That syntax does not support output parameters. – vitaly-t May 20 '17 at 13:07
  • In option-2 First 2 elements are input, but refcursor1 & 2 are named cursors expecting output to be recorded in DB. Same kind i have tried from pg admin, i m getting result and query as below.. . 'select get_data( 'SFO', 'FRA' , 'result1', 'result2' ); FETCH all IN "result1"; FETCH all IN "result2";' – Dinesh May 20 '17 at 17:10
  • That cannot work directly with pg-promise, it requires a special approach with the use of a cursor. See also: http://stackoverflow.com/questions/12101773/does-node-postgres-support-multiple-resultsets – vitaly-t May 20 '17 at 17:25
  • Best is to redesign the function so you can use this approach: https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example#from-database – vitaly-t May 20 '17 at 17:26
  • db.query( { text: "select get_data($1, $2, $3)", values: [ a, b, c ] }, function (err, r) { if (err) { db.query("COMMIT;"); } else { db.query('FETCH ALL FROM ""', function (err, r1) { }); db.query('FETCH ALL FROM ""', function (err, r2) { }); db.query("COMMIT;"); } – Dinesh May 21 '17 at 06:51
  • @vitaly-t Yes, i was trying that as another option suggested in some other search.. But i am giving 3 inputs as shown in below code.., but getting blow error.. error: bind message supplies 1 parameters, but prepared statement "" requires 3. I tried different approaches in values : [] part. but no luck. Did u find any better way. Code i mentioned above. I couldn't format as i am mentioning in comment section. Any idea? – Dinesh May 21 '17 at 06:52

2 Answers2

0

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.

Community
  • 1
  • 1
vitaly-t
  • 24,279
  • 15
  • 116
  • 138
-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); 
});
Stephanie
  • 1