Is there a simple way to handle composite data type?

[gzliudan]

If a composite data type is created in PostgreSQL, such as below:

CREATE TYPE new_data_type AS (
    name            text,
    supplier_id     integer,
    price           double precision
);

Then is there a simple way to define it in libpqxx? assume all data type of each field has been defined in libpqxx already.

[jtv]

There's nothing ready-made, though I imagine it shouldn't be too hard to extract the array-related parsing code for converting from the text format to your custom type on the C++ side.

[jtv]

I had another look, and I think I'll try and build some support for this. But it does raise the question: should the string conversion functions be encoding-aware? So far I've been able to get away with supporting only ASCII (plus basically anything you like in strings). That would still mostly work for arrays and composite types, but not entirely I think.

[gzliudan]

For composite data type, PostgreSQL support ('member1','member2','member3') format

[jtv]

Yes, it's documented. The hard part is dealing with different encodings. For example, when you see a quote in that text, it could be a quote, or it could be just one byte in a completely different Japanese character.

[gzliudan]

We can only support utf8 encoding and refuse input when a quote is not quote.

[gzliudan]

BTW, does libpqxx can support PostgreSQL array of composite data type of by std::vector ?

[jtv]

Yes, you can convert a std::vector or std::array directly to a string in the normal way.

The other direction is more complicated, but it is supported: see the array_parser class.

[gzliudan]

Do you meam: libpqxx can use array_parser convert std::vector to array of postgresql composite data type automatically ? Should I write string_traits::to_buf function for composite data type, such as (‘StringOfFiled1’,‘StringOfFiled2’,‘StringOfFiled3') format string ?

Yes, you can convert a std::vector or std::array directly to a string in the normal way.

The other direction is more complicated, but it is supported: see the array_parser class.

[gzliudan]

Since psql can print composite data type in text format when we run select command in console, I believe there is a way to package the whole struct automatically when each field's string_traits is provied. By use each member field's from_string and to_buf functions, maybe we don't need consider encodings. Only consider format of composite data type, such as single
and double quotation mark, arrays. And maybe we need write a function to tell libpqxx handles which fields.

Yes, it's documented. The hard part is dealing with different encodings. For example, when you see a quote in that text, it could be a quote, or it could be just one byte in a completely different Japanese character.

[jtv]

For now, you'll have to write a full string_traits specialisation for your type, yes. But I'm making first steps towards building in more support. It could become a relatively simple "parse this string and put the values into these fields."

Of course you can use from_string and to_buf and so on on the element types, yes. But encodings are important in the code that looks for the end of a field, before you can even do the encoding. Because that code will have to look for a closing quote, or a comma. And in some encodings you can have a byte with the same value that an ASCII quote or comma would have, except it's not a character but just one part of a multibyte character.

I have released features in the past that only work for encodings where this does not happen. But of course I'd prefer to do it right. This stuff can be hard to retrofit later.

[gzliudan]

I'm work on a project which has many new basic data type and composite data type, all types are ASCII encoding. If there is a feature supports define string_traits for composite data type and array automatically, it will save much time greatly and reduce code errors.

[jtv]

Right now I'm thinking along the lines of a function that parses an SQL composite-type value into a std::tuple. But there may be better ideas once I play with it for a bit. Right now the weather's a little hot for that though!

[gzliudan]

std::tuple is not bad. How about use reflect, such as: https://github.com/EOSIO/fc/blob/master/include/fc/reflect/reflect.hpp ?

[jtv]

Unlikely. I'd rather not add dependencies, but also, I'd need better documentation to be sure that I understood the library correctly.

[jtv]

@gzliudan I just pushed experimental support for parsing the string representation of a value of a composite type into a series of C++ variables. See current master, in include/pqxx/composite.hxx. (You would include it in your code as <pqxx/composite>.) Could you have a look, and see if it suits your needs?

Once we're happy with how this works, I can build it into pqxx::field as well, which will then automatically pass the right encoding group.

And separately from that, I imagine it would be nice to have a way to convert a C++ value to a composite-type string representation as well.

[gzliudan]

I checked composite.hxx. It seems only parse output of postgresql's composite data type. I'm uploading data of composite types to PostgreSQL now. So I will test parse function later when I need to decode data from PostgreSQL. Will you support nested array or composite data types recursively?

[jtv]

Nested arrays are already supported. I haven't figured out yet how nested composite types are represented.

[gzliudan]

Please refer to https://www.postgresql.org/docs/12/rowtypes.html#ROWTYPES-IO-SYNTAX

[jtv]

Of course I did. If you see something that describes nesting of composite types, please quote that specific part.

[gzliudan]

We can use ARRAY and ROW construct some nested composite types, then use select command print it in psql to check the text format of output string. Like this article: https://stackoverflow.com/questions/7363516/how-to-formulate-an-array-literal-of-a-composite-type-containing-arrays

[jtv]

In that case the nesting is just not any kind of special case. Should work as-is.

[gzliudan]

Here's an nested composite type example:

CREATE TYPE key_weight AS 
(
    weight int2,
    key    text
);


CREATE TYPE authority AS 
(
    threshold int4,
    keys      key_weight[]
);

CREATE TYPE producer AS 
(
    name text,
    auth authority[]
);

CREATE TABLE schedules
(
    id serial PRIMARY KEY,
    version   int4 NOT NULL,
    producers producer[] NOT NULL DEFAULT '{}'
);

[jtv]

Thanks. I should incorporate this into a test.

I had some code for converting composite-type values to strings sketched out, but it'll need some more work. The idea is that you'll say "here are my fields, here's the buffer, go convert." It would be convenient for use in writing your own to_buf() etc.

[jtv]

Work on this has been progressing. You can now read a field containing a composite value directly into a sequence of variables:

pqxx::result res = tx.exec("select my_composite from table");
for (auto const row : res)
{
    int a, b;
    float c;
    std::string d;

    row[0].to_composite(a, b, c, d);
    process_values(a / b, c * 3.141, d);
}

[jtv]

Oh, and you can convert a series of values to a string representing a composite value. But I still have to come to grips with exactly how you would use this, with possible quoting and escaping etc.

[KayEss]

row[0].to_composite(a, b, c, d);

Why isn't that from_composite?

[jtv]

Yeah. That's because it's a variant of the to() operation. I admit it's a little awkward — although you could argue that the series of fields is also the client-side equivalent of a "composite."

[jtv]

Barring any catastrophic bugs or great new ideas, I intend to release 7.2 soon, and I'll be closing this bug as well. @KayEss, if you've got anything to add on the naming, this is your chance. :-)

[gzliudan]

I have no objection to closing this issus. Thank you very much.

[KayEss]

Given the Postgres term for this is "composite" it seems a little awkward to use that on the C++ side too. Wouldn't something like to_structure or to_fields or even to_args be better? I admit I can't think of a really good alternative, but I do dislike having something called to_composite not create a Postgres composite. Maybe to_values?

[jtv]

@KayEss okay... how about into_args then? It's nicely consistent with my into<TYPE>(ref) nomenclature.

Of course at some point I'll be wanting to have something very similar on pqxx::row.

[jtv]

Actually, I now see that I did not call those functions into after all! Boy, it's been a while since I worked with the non-const-reference version of to(). Completely forgot what it looked like.

Next proposal: composite_to(). Same word, but it's on the right side of the equation. Expresses the expectation that the SQL side is a composite.

[gzliudan]

How about as_composite or as_tuple ?

[KayEss]

composite_to()

Genius!

[jtv]

@gzliudan I don't think we could do as_composite because the existing as() functions name the C++ type, not the SQL type. "Composite" is the SQL type, not the C++ type. So it sort of gets the relationship between the two types the wrong way around.

I wouldn't want to use "tuple" here because (and this has caused me trouble before) "tuple" already means something on the C++ side and something different on the SQL side.

[jtv]

Support is in the latest release... I'll close this ticket now, and then if there are problems with what I released, we can have a new one.