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.

share|improve this question
    
What you are passing into db.func as parameters can only be input parameters. That syntax does not support output parameters. – vitaly-t 16 hours ago
    
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 12 hours ago
    
That cannot work directly with pg-promise, it requires a special approach with the use of a cursor. See also: stackoverflow.com/questions/12101773/… – vitaly-t 12 hours ago
    
Best is to redesign the function so you can use this approach: github.com/vitaly-t/pg-promise/wiki/… – vitaly-t 12 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.