node.js - How to call a postgresql function that uses refcursors with node-postgres?

Question

I have a function that returns multiple result sets using refcursor. To give you an idea, here is how it is done:

   CREATE OR REPLACE FUNCTION showcars(ref1 refcursor, ref2 refcursor) RETURNS SETOF refcursor AS $$                        
    BEGIN
      OPEN ref1 FOR SELECT model FROM cars WHERE manufacturer = 'Ford';   -- Open the first cursor
      RETURN NEXT ref1;                                                                              -- Return the cursor to the caller
 
      OPEN ref2 FOR SELECT sold FROM sales WHERE manufacturer = 'Ford';   -- Open the second cursor
      RETURN NEXT ref2;                                                                              -- Return the cursor to the caller
    END;
    $$ LANGUAGE plpgsql;

I call the above function like this to get the multiple results back:

BEGIN;

SELECT showcars(_ref1 := 'CarModels', _ref2 := 'CarSales');

FETCH ALL IN "CarModels";
FETCH ALL IN "CarSales";
COMMIT;

As you can see I pass the cursor name as a parameter, so the caller always knows which cursor to fetch. How do I run this from node-postgres to get the results back?

question from:https://stackoverflow.com/questions/65874849/how-to-call-a-postgresql-function-that-uses-refcursors-with-node-postgres

Answer 1

Waitting for answers