libpqxx array data with prepared statements

Question

In PostgreSQL we can store array data in columns. Lets say I have a postgres table like this:

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

Now I am using libpqxx to insert values to this table through my C++ application. This library supports having prepared statements which can have parameters. The query text can contain $1, $2 etc. as placeholders for parameter values that we can provide when you invoke the prepared statement. Here is the documentation for prepared statements.

So lets say I prepared a statement to insert values to this table like so:

pqxx::connection con(dbConnectionString);
con.prepare("sal_empInsert", "INSERT INTO sal_emp VALUES($1, $2, $3)");

For simple parameters like the name, I am pretty sure I can pass a string value as parameter. But I am confused what do I pass for the 2nd and 3rd parameter. Does it need to be an simple array (pointer to the first element), will it work if it is a c++ vector, or does this library have a class for this which I need to initialize first with my array and then pass this object in as parameter?

score 3 · Answer 1 · answered Aug 27 '22 at 14:56

vector can be used to insert an array through prepared statements in PostgreSQL. So in the given example you can insert pay_by_quarter and schedule values in libpqxx as follows:

con.prepare("insert", "INSERT INTO sal_emp VALUES ($1, $2, $3)");
pqxx::work txn(con);

// For example...
std::string name = "John";
std::vector<int> pay_by_quarter = {1000, 2000, 1500};
std::vector<std::vector<std::string>> text = {{"ABC", "123"}, {"PQR", "678"}};
txn.exec_prepared("insert", name, pay_by_quarter, text);
txn.commit();

libpqxx array data with prepared statements

1 Answers1