node-postgres是否支持多个结果集 [英] does node-postgres support multiple resultsets

问题描述

我有一个PostgresQL函数，它返回多个结果集。我可以毫无问题地在.net中提取这些结果集（所以我知道我的函数可以正常工作），但是我在使用node-postgres时遇到了麻烦。

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.

结果对象返回一个包含7个项目的数组，该数组与返回的数据集数量相匹配。

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

在Node中，这7行中的每一行仅包含<未命名门户1> 的字符串。 / p>

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.
  }

所以：node-postgres是否支持多个结果集，如果是，是否有关于提取的建议？

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

编辑：这是我将来与node-postgres一起使用的代码，如果将来有人需要使用它。

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

});

推荐答案

更新：请参见< a href = http://www.sqlines.com/postgresql/how-to/return_result_set_from_stored_procedure>这篇出色的教程，其中介绍了如何获取和管理refcursors。

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

由于node-postgres无法识别您要作为结果集句柄返回的refrefsors，因此它似乎不支持PostgreSQL的多个结果集。这很公平，因为PostgreSQL也不真正支持多个结果集，它们只是用反射器模拟。

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.

您可以 FETCH refcursor 中的c $ c> .html> SQL级光标命令，尽管它的文档很糟糕。您无需使用 PL / PgSQL 游标处理即可。只是：

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>";

请注意双引号，这很重要。将从函数返回的refcursor名称替换为<未命名门户1> 。

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

创建的refcursor必须仍然打开，除非创建了光标 WITH HOLD 。当事务提交或回滚时，非 HOLD 游标将关闭。

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.

例如，给定虚拟refcursor-返回函数：

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';

...这会返回一组游标，您可以通过将门户名称传递给 FETCH ，例如：

... 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=# 

这篇关于node-postgres是否支持多个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！