Params question

[pkfox]

Just to be clear this question relates to a hobby project

Is it common practice to create a new Params object when calling a function in a loop, I ask because I notice there is no clear() on Params and if they do have values you can't change them. I'm probably not doing this the optimal way but, I have a large json file and I want to put its objects in a postgresql table ( 1 object = 1 row ) I have code that reads the json file and returns a serialized object using one of my classes, and when I come to push to pg i use the code below , I commit at the end of processing , am I doing this properly ?

size_t PGConnect::WriteToPG(const Bigfoot bf)
{
try
	{
	pqxx::params Params;
	std::size_t RetVal = 0;
	Params.append(bf.name);
	Params.append(bf.value);
	Params.append(bf.isActive);
	Params.append(bf.timestamp);
	Params.append(bf.tags);
	Params.append(bf.description);
	Params.append(bf.pad);


	
		this->PGResult = this->PGTransaction.exec("select createbigfoot($1,$2,$3,$4,$5,$6,$7)", Params);
		RetVal = PGResult[0][0].as<size_t>();
	}
	catch (const std::exception& e)
	{
		std::cerr << "Error writing to database: " << e.what() << std::endl;
		return 0;
	}
	return RetVal;
}

[pkfox]

A one liner run in psql courtesy of Mr ChatGPT

INSERT INTO bigfoot (jname, value, tags, description, jpad, is_active, timestamp_utc)
SELECT
    jname,
    value,
    tags,
    description,
    jpad,
    is_active,
    timestamp_utc
FROM jsonb_to_recordset(
    pg_read_file('/home/pjk/Projects/json/newbigfoot.json')::jsonb
) AS x(
    jname text,
    value int,
    tags text[],
    description text,
    jpad text,
    is_active bool,
    timestamp_utc timestamptz
);

[pkfox]

Took a couple of seconds for a 200mb file

[jtv]

Good to hear @pkfox. A one-off job where you don't even need to transfer data to the client... yes, definitely more of a job for psql!

For what it's worth, your parameters code did look about right to me, but there's an easier way.

Instead of...

        pqxx::params Params;
        Params.append(bf.name);
        Params.append(bf.value);
        Params.append(bf.isActive);
        Params.append(bf.timestamp);
        Params.append(bf.tags);
        Params.append(bf.description);
        Params.append(bf.pad);

...you can just write:

        pqxx::params Params{
            bf.name, bf.value, bf.isActive, bf.timestamp, bf.tags,
            bf.description, bf.pad};

You could even inline that in your call to exec() if you wanted.

And, at least in the upcoming 8.0, you don't need to deal with a result value. Instead of exec() you can call query_value() to extract the single value from the query and convert it.

So the whole call could become just:

    return this->PGTransaction.query_value<std::size_t>(
        "select createbigfoot($1,$2,$3,$4,$5,$6,$7)",
        pqxx::params{
            bf.name, bf.value, bf.isActive, bf.timestamp, bf.tags, bf.description, bf.pad});

Doesn't help now of course, but just in case it becomes useful in the future. 😄

[pkfox]

Thanks @jtv while I have your ear
I make this call in my code which you'll have to take my word for returns
a value

pqxx::params Params;
int id = 42;
Params.append(id);

pqxx::result r = this->PGTransaction.exec("SELECT getbigfoot($1)", Params);
pqxx::row row = r.one_row();

if(!row[0].is_null()) 
	std::cout << "row: " << row[0] << std::endl;

which outputs

row: (42,"Item 42",846403,t,"2025-11-15 18:01:00+00","{musk,mussel,muted,mutter,mylars,myra}","id: 42 value: 846403",pad42)

I want to populate an instance of this class with those values

class Bigfoot
{
public:
    int id;
    std::string name;
    int value;
    bool isActive;
    std::string timestamp;
    std::vector<std::string> tags;
    std::string description;
    std::string pad;
    void LoadFromJson(std::string json);
    std::string ToString();
};

I can do it by parsing the raw string but I'm sure there is a proper way
Any help much appreciated thanks for your reply

[pkfox]

Added a question

[jtv]

(I took the liberty of editing your posts: the syntax for a code block is three backticks on a separate line, or if you want syntax colouring, three backticks immediately followed by "cxx".)

It looks to me as if perhaps what you want is a composite type. See the definitions in include/pqxx/composite.hxx.

[pkfox]

@jtv Pardon the pun but is that a template for writing my own or can I use this as is

[jtv]

The idea is that you specialise a pqxx::string_traits<Bigfoot> and implement its functions, but you can use the functions in composite.hxx as helpers to make that easier.

With that in place, libpqxx will magically know how to convert between Bigfoot objects and their SQL representation. So you can pass the object as a parameter, use pqxx::to_string() and pqxx::from_string(), and so on.

(This all gets easier in libpqxx 8.x, where there's fewer functions to implement.)

If you wanted to though you can also just call the helpers without doing the string_traits.

[pkfox]

Thanks @jtv when you say If you wanted to though you can also just call the helpers without doing the string_traits. How ?

[jtv]

Maybe have a look at the 8.0 release branch in #914. It improves on that header.

[pkfox]

@jtv Couldn't see anything to help me in there (you know me and string_traits) can I call this

inline void parse_composite(std::string_view text, T &...fields)
{
  parse_composite(pqxx::internal::encoding_group::MONOBYTE, text, fields...);
} 

pqxx::parse_composite(my field list);

[jtv]

@pkfox do look at the 8.0 release branch version of that file. Yes, you can just call that function to parse SQL strings like the one you gave.

See also test/test_composite.cxx for examples.

[jtv]

Oh by the way your reaction to string_traits was probably what drove me to simplify that whole API!