What are the various methods available to return a single value from pg

[pkfox]

What are the various methods available to return a single value from pg ? I know these ones where tx is a pqxx::work transaction

std::vector<std::string> Attempts;

pqxx::array<std::string> arr = tx.query_value<pqxx::array<std::vector<std::string>>("your query");
Attempts.assign(arr.begin(), arr.end());

and streaming 
for (auto [arr] : tx.stream<pqxx::array<std::string>>("your query")
{
	Attempts.assign(arr.begin(), arr.end());
}

Are there any more ?

Kind regards

Pete Kane

[jtv]

For just querying a single value, nothing is going to beat query_value(). But if you want something that's not quite so specialised to that one job, there are many options.

(Don't use streaming for a single value — there's a bit of extra startup cost that makes streaming slightly slower for small results. Unless the value is very large, maybe, but I haven't tested that.)

There's also transaction_base::query() and its callback-based equivalent. Or you could use tx.exec(...)[0][0], or call tx.exec(...).one_row() and read the value from the row you get, in one of myriad ways. (In 8.0, as long as you're willing to fit it within the same statement, you can use one_row_ref() instead for extra efficiency.)

It's really quite an extensive API because it all depends on your needs and preferences. Some people prefer callbacks, and libpqxx can actually figure out the type of the value you want at compile time, and then at run time do the conversion from SQL text to that type.

[pkfox]

@jtv If the query returned a setof wordleanswer what would the strongly typed answers be

[jtv]

I must admit I don't use them very much but AFAIK a setof wordleanswer would come out as a result set of some number of rows and 1 column. Each row would hold one wordleanswer.

That means it's not actually a situation of retrieving a single value! And so it's not directly suitable for tx.query_value<...>(...). You could just do a regular tx.query<WordleAnswer>(...), and get a pqxx::result of single-column rows, each holding one WordleAnswer.

Or, if you really want it to be a single array, you could do something like...

std::vector<WordleAnswer> const answers =
    tx.query_value<std::vector<WordleAnswer>>(
        "SELECT array_agg(answer) FROM (SELECT answer FROM wordle_answers)");

That's a bit circuitous: the SQL side queries all the answers, one per row, and then folds them all into a single SQL array. The C++ side then reads that array as a std::vector. There's probably a better way to do it.

[pkfox]

I see thanks @jtv

[pkfox]

I must admit I don't use them very much but AFAIK a setof wordleanswer would come out as a result set of some number of rows and 1 column. Each row would hold one wordleanswer.

That means it's not actually a situation of retrieving a single value! And so it's not directly suitable for tx.query_value<...>(...). You could just do a regular tx.query<WordleAnswer>(...), and get a pqxx::result of single-column rows, each holding one WordleAnswer.

Or, if you really want it to be a single array, you could do something like...

std::vector<WordleAnswer> const answers =
    tx.query_value<std::vector<WordleAnswer>>(
        "SELECT array_agg(answer) FROM (SELECT answer FROM wordle_answers)");

That's a bit circuitous: the SQL side queries all the answers, one per row, and then folds them all into a single SQL array. The C++ side then reads that array as a std::vector. There's probably a better way to do it.

Yes @jtv the way you were doing it you were just pulling one column I rarely do that on pg WordleAnswer is a composite and I pull a setof

pqxx::work tx = this->da.GetTransaction();
this->WordleAnswers.clear(); // This is a vector
std::stringstream oss{ "Select getwordleanswers()" };
std::string sql(oss.str());
	
pqxx::internal::result_iteration<WordleAnswer> answers = tx.query<WordleAnswer>(sql);

for(auto [x] : answers)
    this->AddAttempt(x);

If only I could get rid of that auto

[jtv]

Actually I'd make answers an auto as well! Its type is in the pqxx::internal namespace so details may change.

[pkfox]

@jtv I'm a bit of a purist as far as strongly typed languages go ( and anything else when I think about it ) you have var in DotNet and auto in C++ I don't like using either of them - just sayin... 😊

[jtv]

That's fine, but in this case the type is a bit of a "none of your business" implementation detail. And where possible I think I'd even like such objects to be nameless temporaries, allowing me maximum freedom in improving the implementation later without breaking application code.

[pkfox]

I think we're done here thanks @jtv