Can you return a setof<T> into a std::vector

[pkfox]

Just to be clear this question relates to a hobby project

I have this class

#pragma once
#include "baseclass.h"
#include "stringhelper.h"
#include "wordlelevelhelper.h"

#include <string>
#include <cpprest/http_client.h>

class WordleAnswer
{ 
	public:
	WordleAnswer();
	std::string ToString() ;
	std::string GetJSON()  ;
	int id;
	std::string Answer;
	std::string  WordleDate;
	std::string  StarterWord;
	std::string  Notes;
	int Attempts;
	bool Solved;
	std::vector<std::string> Attemptslist;
	Wordlelevel WordleLevel;
};

No prizes for guessing my project, with the help of @jtv and others on here I've written string_traits to handle sending and retrieving this struct from pg, before I pull what is left of my hair out I thought I'd ask you knowledgeable lot if it's doable to return a pg setof wordleanswer into a std::vector at the moment I can retrieve a single with code as below where r is a pqxx::result

WordleAnswer wa = r[0][0].as<WordleAnswer>(); 

And I would like to be able to do

std::vector<WordleAnswer> was = r[0][0].as<std::vector<WordleAnswer>>(); 
Any help much appreciated

[jtv]

I guess the hard part of that is folding a single-column, multi-row result into a single-row, single-column result containing just one SQL array.

Would something like this work? On the SQL side, read a bunch of answers into a single SQL array.

SELECT ARRAY(
    SELECT answer
    FROM answers
);

Of course the query will need changes to suit your situation. On the C++ side, you execute your query, and extract its single field of output as a std::vector<WordleAnswer>::

std::vector<WordleAnswer> get_answers(pqxx::transaction_base &tx)
{
    return tx.query_value<std::vector<WordleAnswer>>(answer_query);
}

(The query_value<TYPE>(...) call is shorthand for exec(...)[0][0].as<TYPE>().)

[jtv]

Er... I do think that requires 8.0 though.

[jtv]

Oh and I'm assuming that the query inside the SELECT ARRAY(...) is something that returns a SETOF.

[pkfox]

@jtv Thanks would it help if I showed you the function ?

[jtv]

Well honestly I'm kind of exhausted for the day... But if it returns a SETOF, I think that comes out as a series of rows.

[pkfox]

@jtv No problem 👍

[pkfox]

I don't think it can be done unless the function returns a proper array not a setof

[jtv]

I tried it with int... The ARRAY(...) turns it from a result set into an array.

[pkfox]

@jtv 👍 Hurry up and release 8 😊