how to work with refcursor?

[stan-yu]

Please add documentation and examples on working with refcursors. When calling a procedure with Connection::call() that has an INOUT refcursor parameter, in the lambda handler, in row, one record with one column is returned, with the value <unnamed portal 1>.

Procedure example:
CREATE OR REPLACE PROCEDURE public.get_objects(
IN pstartofinterval bigint,
IN pendofinterval bigint,
INOUT cr refcursor);

In my example, the refcursor cr should return jsonb objects. The pstartofinterval and pendofinterval parameters determine which specific objects to return. In this case, it doesn't matter which. The question is how to get them.

Example code:

pgfe::Connection conn(pgfe::Connection_options{}.set(pgfe::Communication_mode::net).set_hostname( ...)....;

auto get_values = [&json_value_callback](pgfe::Row&& row) {
  std::cout << row.field_count() << std::endl;    // output 1
  std::cout << row.is_empty() << std::endl;       // output 0
  for (size_t i = 0; i < row.field_count(); ++i) {
    std::cout << row.field_name(i) << std::endl;   // output "cr"
  }
  std::cout << pgfe::to<std::string>(row[0]) << std::endl;     // output "<unnamed portal 1>"
};

conn.call(get_values, "public.get_objects", start_of_interval, end_of_interval, nullptr);

Thanks.

[dmitigr]

@stan-yu Hello!

As explained in 43.7.3.5. of PostgreSQL documentation at you can work with the returned cursor by using the corresponding SQL commands like FETCH.

But I would suggest you to return TABLE or SETOF from a function with RETURN NEXT or RETURN QUERY clauses, described here, instead of using cursors.

[dmitigr]

@dmitigr Probably it would be nice to add a convenient class to work with cursors.

[stan-yu]

@dmitigr hi. yes, thanks for the info. But, unfortunately, our project has already implemented a solution with a refcursor.
I will give an example of a solution with a cursor, using the current capabilities of the dmitigr::pgfe library, that we used. Perhaps someone will be useful.
In this example calls the public.get_objects() procedure described above. pgfe::Transaction_guard is needed to ensure that a transaction is not committ(or rollback) until the cursor has finished reading. Calling a procedure and reading from a cursor(fetch) must be called with different commands, as shown below.

pgfe::Connection conn(pgfe::Connection_options{}.set(pgfe::Communication_mode::net).set_hostname( ...)....;
pgfe::Transaction_guard tg{*conn_};
conn.call("public.get_objects", start_of_interval, end_of_interval, "cr");
conn.execute([](pgfe::Row&& row) { std::cout << pgfe::to<std::string>(row[0]) << std::endl ; },
               "fetch all from cr");