does node-postgres support multiple resultsets

Question

I have a PostgresQL function that returns multiple resultsets. I can extract these resultsets in .net without a problem (so I know my function works correctly), but I am having trouble doing so with node-postgres.

The result object returns an array of 7 items which matches the number of datasets returned.

In Node, the each of the 7 rows simply contains a string of <unnamed portal 1>.

connection.query("BEGIN");
connection.query({text: "SELECT getoperationaldatasetmodel($1)", values : [clientid]}, function(err, results) {


  if (err) {
    connection.query("COMMIT");
    self.pool.release(connection);
    callback(err);
  }
  else {
    var opsDataset = null;
    var rows = results.rows;
    // this returns 7 rows but the rows do not contain data but rather the name of the dataset.
  }

So: does node-postgres support multiple result sets and if yes, any suggestions on how to extract?

EDIT: Here is the code I used with node-postgres should someone else need to use it in the future.

// must wrap in a transaction otherwise won't be able to see the multiple sets.
connection.query("BEGIN");
connection.query({text: "SELECT myfunction($1)", values : [clientid]}, function(err, results) {

  if (err) {

     // handle error here
     connection.query("COMMIT;");
  }
  else {

    connection.query('FETCH ALL FROM "<unnamed portal 1>"',  function(err, r1) {
        // r1.rows will contain the data for the first refcursor
    });
    connection.query('FETCH ALL FROM "<unnamed portal 2>"',  function(err, r2) {
        // r2.rows will contain the data for the second refcursor
    });

    // remember to handle the closure of the transaction

});

http://stackoverflow.com/questions/6674787/calling-a-function-that-returns-a-refcursor — Craig Ringer, Aug 24 '12 at 01:19
http://stackoverflow.com/questions/11135815/how-to-select-all-rows-from-refcursor-returned-by-pl-pgsql-function — Craig Ringer, Aug 24 '12 at 01:19
... neither of which actually provide answers about how to fetch from a refcursor. Sorry. It looks like it's either badly under-documented or a real pain to fetch rows from a `refcursor` in SQL. — Craig Ringer, Aug 24 '12 at 01:26
Just checked with Brian Carlson, the creator, and he says he has not done anything to support refcursors. I'm going to raise a GIT issue to see if it can be addressed. — AlexGad, Aug 24 '12 at 01:52
Alex, why did you want multiple result sets in the first place? Were you stuck with the postgres function? Or did you, as I do, assume that it would be faster to do all the DB queries in one hit and return all the results in a bundle than do multiple server-to-DB roundtrips? If the latter, did the "multi-resultset approach" end up being faster? — poshest, Oct 29 '14 at 11:37

Craig Ringer · Accepted Answer · 2012-08-24T01:49:16.757

UPDATE: See this excellent tutorial for an explanation of how to fetch and manage refcursors.

Since node-postgres isn't recognising the refcursors you're returning as result set handles, it seems likely that it doesn't support multiple result sets from PostgreSQL. That's fair enough as PostgreSQL doesn't really support multiple result sets either, they're just emulated with refcursors.

You can FETCH from a refcursor via SQL-level cursor commands SQL-level cursor commands, though the documentation for it is miserable. You don't need to use PL/PgSQL cursor handling to do it. Just:

FETCH ALL FROM "<unnamed portal 1>";

Note the double quotes, which are important. Subtitute the refcursor name returned from your function for <unnamed portal 1>.

Note also that the transaction that created the refcursor must still be open unless the cursor was created WITH HOLD. Non-HOLD cursors are closed when the transaction commits or rolls back.

For example, given the dummy refcursor-returning function:

CREATE OR REPLACE FUNCTION dummy_cursor_returning_fn() RETURNS SETOF refcursor AS $$
DECLARE
    curs1 refcursor;
    curs2 refcursor;
BEGIN
    OPEN curs1 FOR SELECT generate_series(1,4);
    OPEN curs2 FOR SELECT generate_series(5,8);
    RETURN NEXT curs1;
    RETURN NEXT curs2;
    RETURN;
END;
$$ LANGUAGE 'plpgsql';

... which returns a set of cursors, you can get the results by passing the portal names to FETCH, eg:

regress=# BEGIN;
BEGIN
regress=# SELECT dummy_cursor_returning_fn();
 dummy_cursor_returning_fn 
---------------------------
 <unnamed portal 7>
 <unnamed portal 8>
(2 rows)

regress=# FETCH ALL FROM "<unnamed portal 7>";
 generate_series 
-----------------
               1
               2
               3
               4
(4 rows)

regress=# FETCH ALL FROM "<unnamed portal 8>";
 generate_series 
-----------------
               5
               6
               7
               8
(4 rows)

regress=#

Absolutely awesome!!! Totally solved my problem. I am going to provide the code I used with node-postgres to specifically solve it for others, but I've marked your answer as the correct one. Thanks, definitely helped me keep whatever hair I have left ;). — AlexGad, Aug 24 '12 at 02:02

does node-postgres support multiple resultsets

1 Answers1

Linked