Can I use stream_to to insert multiple records while one field is Postgis Geometry type?

[Yang-NFS]

My code is like this:

        for (auto& storm : storms) {
            stream << std::tuple<int, float, std::string>{
                int(storm.level),
                    storm.height,
                    std::string("ST_GeomFromText('POINT(") + std::to_string(storm.lon) + " " + std::to_string(storm.lat) + ")', 4326)"
            };
        }

The geom is not string type but a geometry type. I knew the above code doesn't work. The other way is to join the insert statement manually which I don't want. And the points are quite a lot, maybe more than 10,000.

So.. can I use stream_to for this case?

[jtv]

That should work, but you'll need a C++ type (such as a class or struct) to represent the PostGIS SQL type on the C++ side of the connection.

Then, you'll need to build support for libpqxx to convert that type to/from strings in their SQL format. See include/pqxx/doc/datatypes.md for instructions on that.

[Yang-NFS]

I have tried a lot but still no working. My code is:

struct MyRawField
{
    std::string content;
};
namespace pqxx
{
template<> std::string const type_name<MyRawField>{"MyRawField"};
template<> inline constexpr bool is_unquoted_safe<MyRawField>{true};
template<> struct nullness<MyRawField> : pqxx::no_null<MyRawField> {};
template<> struct string_traits<MyRawField>
{
    static constexpr bool converts_to_string{true};
    static constexpr bool converts_from_string{false};

    static zview to_buf(char *begin, char *end, MyRawField const &value) {
        std::cout << "to_buf:" << value.content << std::endl;
        return generic_to_buf(begin, end, value);
    }
    static char *into_buf(char *begin, char *end, MyRawField const &value) {
        std::cout << "into_buf:" << value.content << std::endl;
        memcpy(begin, value.content.c_str(), value.content.length());
        begin += value.content.length();
        *begin++ = '\0';
        return begin;
    }
    static std::size_t size_buffer(MyRawField const &value) noexcept {
        std::cout << "size_buffer:" << value.content << std::endl;
        return value.content.length() + 1;
    }
};
};

[Yang-NFS]

The error from postgres is:

ERROR:  parse error - invalid geometry
HINT: "ST" <-- parse error at position 2 within geometry

Looking forward your help. Thanks

[jtv]

I take it this happened when you passed a MyRawField to a prepared or parameterised statement?

What's the string value inside that MyRawField? What do you get if you just call pqxx::to_string() on that value? How would you write that value if you were typing it in SQL? When you write it in SQL, do you need quotes?

[Yang-NFS]

Thanks for your time. Sorry. It's my fault. Actually there is no need to customize struct for this case. For stream_to, I should use WKT to represent geometry instead of ST_SetSRID(ST_MakePoint(lng,lat),4326) in the insert statement.