How to pass custom class to postgres function

[pkfox]

How to pass custom class to postgres function

[jtv]

Most of the real-world examples are in include/pqxx/internal/conversions.hxx, but the test suite also defines a few for testing, e.g. in test/test_types.hxx. But grep for string_traits in the test/ part of the source tree and you'll find more.

Some of those are pretty incomplete, and that's often fine so long as it doesn't break compilation. It's all compile-time work, after all. But I haven't documented all of the cases where you can get away with it — in principle it's better to be complete.

[jtv]

And thank you for bringing it up. This is how we keep improving things.

[jtv]

Why has this been closed ?

It got closed automatically because I finally merged the PR attached to it. I'm a bit overloaded at the moment so I've kind of lost track... Anything else I can do for this ticket?

[pkfox]

Only show me how to do what I want to do :-)

[jtv]

Sorry I've been a bit distracted. The central question is: what do instances of your SQL type look like, going into the database and coming out of the database in an SQL session?

So this is more to do with the SQL type than the C++ type. The "going in" and "coming out" formats can differ sometimes. And sometimes on the "going in" side there's a choice of either special syntax or an SQL string that you cast, e.g. '2 weeks::interval`'. For libpqxx conversions you need the latter.

Also, look up support for "composite" types in the libpqxx headers. I believe your type is one of those.

[jtv]

Folks, I'm sorry I haven't been very helpful here — I'm on a borrowed laptop since mine is broken, and some other matters demanded my attention. I can access tickets on my phone, but it's pretty difficult to keep an overview.

@pkfox I've sort of got things working on this borrowed laptop now, so I'll try to answer the questions better. And I now understand that things can be much, much simpler!

I think @tt4g put his finger on an important point that I missed. Your original question used CREATE TYPE, which implied that you wanted to store instances of your class to a single column in a table. That way, each instance of your class is a single value in SQL.

But that's quite an advanced thing to do. It's not something most SQL developers ever need to do at all, and I mistakenly assumed that you were aware of this. So, with apologies, forget everything I've told you. I tried to give you what you asked for rather than what you wanted.

You see, when you store an object in a database, a client library like libpqxx generally doesn't even know about your class. Your C++ class is normally completely irrelevant at this level, and you don't need to CREATE TYPE at all. It's actually simpler than that.

Instead, when you initially set up your database, you create a table to represent your class. Its columns are the fields in your class. And each instance of the class is a row in the table.

So going back to your original class, this is what you do when you initially set up your database:

CREATE TABLE MersennePrime
(
    mersenne int8 PRIMARY KEY,
    mersenneresult text,
    probability text
);

(I'm assuming here that the mersenne field acts as a key, i.e. every instance must have a unique mersenne field, and that's how you identify the instances.)

Your C++ code can then store an object of your class like this:

// Write a Mersenne prime to the database.
// The prime must not exist in the database yet; if it does, that's a primary key uniqueness violation.
void store_prime(pqxx::work &tx, MersennePrime const &prime)
{
    tx.exec0(
        "INSERT INTO MersennePrime(mersenne, mersenneresult, probability) "
        "VALUES (" +
        pqxx::quote(prime.Mersenne) + ", " +
        pqxx::quote(prime.Result) + ", " +
        pqxx::quote(prime.PrimeProbability) +
        ")"
    );
}

For retrieving primes, you can do things like this:

// Read a Mersenne prime from the database, which must already exist.
MersennePrime read_prime(pqxx::work &tx, int prime)
{
    // exec1() executes your query, checks that it returns exactly 1 row, and returns it.
    // If you get zero rows, or more than one, it throws an exception.
    pqxx::row r = tx.exec1(
        "SELECT mersenneresult, probability "
        "WHERE mersenne = " + pqxx::quote(prime)
    );
    // (This code is a bit convoluted because MersennePrime doesn't have a constructor that sets all its fields to given values.)
    MersennePrime val;
    val.mersenne = prime;
    val.Result = r[0].as<std::string>();  // Read the first field, as a string.
    val.PrimeProbability = r[1].as<std::string>();  // Read the second field, as a string.
    return val;
}

// Try to read a Mersenne prime from the database.
// If it does not exist, return an empty value.
std::optional<MersennePrime> try_read_prime(pqxx::work &tx, int prime)
{
    // exec01() executes your query, checks that it returns no more than one row, and returns it.
    // If the query returns 0 rows, it returns an empty `std::optional`.
    // We don't query the mersenne column, since we're already given that as a parameter.
    std::optional<pqxx::row> r = tx.exec01(
        "SELECT mersenneresult, probability "
        "WHERE mersenne = " + pqxx::quote(prime)
    );
    if (r)
    {
        // Construct a MersennePrime.  Again could be easier with the right constructor.
        MersennePrime p;
        p.mersenne = prime;
        p.Result = r[0].as<std::string>();
        p.PrimeProbability = r[1].as<std::string>();
        return {p};
    }
    return {};
}

// Read all Mersenne primes from the database, in order.
std::vector<MersennePrime> read_all_primes(pqxx::work &tx)
{
    std::vector<MersennePrime> all;
    // query() executes a query and iterates over the results, one row at a time,
    // converting each field in the row to values of types you specify.
    for (auto [mersenne, result, probability] : query<int, std::string, std::string>(
        "SELECT mersenne, mersenneresult, probability "
        "FROM mersenneprime "
        "ORDER BY mersenne")
    )
    {
        MersennePrime prime;
        prime.mersenneprime = mersenne;
        prime.mersenneresult = result;
        prime.probability = probability;
        // This isn't too efficient, since extending the array on the fly may have to reallocate its memory.
        // If we're dealing with large numbers of primes, we may want to iterate instead of storing in a vector.
        // Also, with a more helpful constructor, we could use all.emplace_back(...).
        all.push_back(prime);
    }
    return all;
|

Of course all of this could be made a lot more efficient, if that matters for your application. And it could be simpler if your class had a constructor. And you may want to do other things. But I think this shows the basics.

[jtv]

Nothing wrong with an honest answer!

I agree that building SQL on the fly doesn't feel very nice, but for that we have parameterised statements.

For passing parameters to functions etc. however I really recommend going with what basically everybody does in SQL, and treat an object as a bag of values.

The code outline you posted before looked structurally OK to me, if that's the way you want to go. But over time I expect that keeping your class definition aligned between your database(s), your own application, and the libpqxx conversions will probably feel less neat in practice.

[KayEss]

As jeroen asks, if your real purpose is to learn the more advanced C++ techniques then I would recommend that you go somewhere like the include.cpp discord server where there's a lot more people who can help you with the C++ aspects.

[jtv]

Have a look here
Composite types

Enums are not a great example here - in libpqxx you just call a preprocessor macro and you've got support for your enum. Composite types are a somewhat different story.

Oh never mind - my eyesight is terrible so reading isn't always easy.

[jtv]

What the C# library does there, as far as I can tell, is use introspection to figure out at run time what's in your class, and map that.

C++ sadly does not have introspection yet.

[jtv]

I think it's clear!

You do it by defining a C++ type, to correspond to your SQL type; and then you define how libpqxx should convert values of this type to and from PostgreSQL's text format.

It's a bit of work. It's documented in datetypes.md. Be warned that this can change between libpqxx versions.

[pkfox]

Hi and thanks for your response - do you have some sample code that shows how one would do this (I learn from code much easier than documentation) What files would I put all the template stuff in ? thanks again

[pkfox]

Thanks Jeroen I'll take a look - this template stuff is new to me
Edit
Had a look but man that stuff is WAY over my head - I've been spoilt using C# and npgsql , it's a one liner on the DataSourceBuilder
this.DataSourceBuilder.MapComposite<MyClass>("myclass");
End edit
Thanks for trying to help - I think for people like me a useful example would be a
THIS IS HOW YOU CAN SEND YOUR C++ CLASS DIRECTLY TO POSTGRES (as that is all I need to do to update a ecord)
Thanks again Jeroen

[jtv]

I'll have to mull that one over for a bit... The API for this is designed for extensibility and minimal run-time overhead. I think thorough familiarity with templates will always be required, and the "traits" pattern seems like the right way to do it. It's a very powerful pattern.

But I can ensure that the link to the datatypes document is clear in the documentation for retrieving results & calling parameterised queries.

[pkfox]

Thanks for your patience with me Jeroen

[pkfox]

Do you want to close this now ? or will you post if you have any news ? I'm intrigued to see how this can be achieved, once I see what is required for my small class all will become clear I'm sure.

[jtv]

Let's keep it open so I don't forget. But I'm afraid you will have to have an understanding of template specialisation for this, as well as the traits pattern.

I can give you a quick rundown: include/pqxx/strconv.hxx defines a few templates that represent some generic structs and variables describing some type. (I usually, rather unimaginatively, referred to the type they describe as TYPE.)

But in this case, the templates are not meant for anyone to instantiate. If your own data type is called X, then you can generally just use std::vector<X> and that will give you a working vector. That is not the case for string_traits etc. They do not contain the actual code that is needed in order to convert X to and from PostgreSQL's string format.

Instead, you need to specialise these templates. You need to define pqxx::string_traits<X> and so on. And that is what the code in include/pqxx/internal/conversions.hxx does, for lots of standard types. And that is how conversions.hxx creates conversion support for all these types.

[jtv]

Hi @pkfox, I've got a PR up that I hope will make it a bit easier to find out the background on data type conversions: #761. (It also tries to fix a problem with the readthedocs build, which got broken by a change to how that site works.)

[pkfox]

Hi @pkfox, I've got a PR up that I hope will make it a bit easier to find out the background on data type conversions: #761. (It also tries to fix a problem with the readthedocs build, which got broken by a change to how that site works.)

Thanks Jeroen I do appreciate it
Regards
Pete Kane

[pkfox]

Why has this been closed ?

[jtv]

I see! Have you read the documentation in datatypes.md, and looked at how strconv.h goes through the steps documented there?

You'll have to have some knowledge of template specialisation, but not a lot. Look at the definition of e.g. string_traits<bool>, nullness<bool>, and type_name<bool> - you'll need to write similar definitions for your own type. Mainly they should contain code to represent your C++ type in SQL form, and vice versa.

Then, search for the definition of string_traits<std::year_month_day> - I think it's in a different header but I can't easily check right now. That'll give you an example of converting a more complex type.

Of course you'll have to know how to write an instance of your type in SQL, and what an instance will look like when SQL represents it as text.

[pkfox]

@jtv Yes I have read that but what I need is, a complete example of what is required to use a custom class/type, even if the class has only one property. e.g. where(as in what file/files ? ) do I need to put all this specialisation and string_traits stuff in. Apologies for dumb questions

[jtv]

No dumb questions. Write the definitions for the conversions in a header of your own project, preferably somewhere just below the definition of your type itself, so that any code that (implicitly) makes use of these conversions will always be compiled in a context where the conversions are defined.

These conversion definitions must be in the pqxx namespace. (Of course your data type itself should not be, since it's part of your application. The conversion builds the bridge to its use in libpqxx, as it were.)

[pkfox]

My class only has a string and an int - why do I need conversions ? and convert them to what ?

[jtv]

The conversion is between the C++ types which your application handles, and PostgreSQL's string format.

For example, when you query an integer value from the database, the database will send it as a string such as "1234". If your application then retrieves that value as an int, libpqxx's string conversions will parse that string and give you the number 1234 instead.

You do have the option of just reading the values as strings. For example, a pqxx::result field has a method that reads the value as a const char *. Or you can use the text conversion mechanism to "convert" to a std::string or (where appropriate) a std::string_view.

[pkfox]

Ok, let's assume my class has only one property which is a string, and I want to send an instance of it to a pg function - what needs doing at the application end

[pkfox]

I give up

[pkfox]

I can't believe someone here hasn't already done what I'm asking

[tt4g]

I can't believe someone here hasn't already done what I'm asking

@pkfox I don't understand why you think that way when advice has been given to you many times here.

You read the following documentation and source code and couldn't figure out how to send custom types to libpq?

• libpqxx: Supporting additional data types
• https://github.com/jtv/libpqxx/blob/7.8.1/include/pqxx/strconv.hxx

You just asked an abstract question "what should I do?" after the advice, so no one here could understand what exactly you were having trouble with, and we couldn't help you accurately.
If so, you should write the source code for your custom data types nullness, string_traits and type_name and ask "I don't understand this part.

[pkfox]

Hi @tt4g, Jeroen knows exactly what I would like to do, what I'm not clear about is where to put the source code and what I need to code - a simple example would help me immensly, I glaze over wading through reams of template code in the examples. It's largely my ignorance of templating

[tt4g]

where to put the source code

Save the header file that defines the template class as part of your project since it is the source code for your project.

what I need to code

As written in the following comments, nullness and string_traits are required.
type_name may be required.

• #757 (comment)
• #757 (comment)

An example of a minimal custom type can be found in the following test.

https://github.com/jtv/libpqxx/blob/7.8.1/test/test_types.hxx#L79-L143

[pkfox]

Hi @tt4g I'm going to try this one step at a time with your guidance ( if that is okay with you )
I have the code below in its own header file

namespace pqxx
{
	template<> struct nullness<pjk::MersennePrime>:no_null<pjk::MersennePrime>{};
	template<> struct string_traits<pjk::MersennePrime>
	{
		bool converts_to_string{ true };
		bool converts_from_string{ true };
	};
}

and my class is

namespace pjk
{
	class MersennePrime
	{
	public:
		int Mersenne;
		std::string Result;
		int PrimeProbability;
	};
}

it compiles ok which is a start - what else do I need to do ? thanks for your time and patience

[tt4g]

Add functions to_buf, into_buf, size_buffer and from_string to string_traits as described in the documentation: https://libpqxx.readthedocs.io/en/7.8.1/a01465.html
Then pass A to the libpqxx function.

[pkfox]

I now have this which I copied from your link replacing T with MersennePrime , presumably I need to implement the methods ? Once again thanks for your help and patience

#ifndef MPCONV_H
#define MPCONV_H

#include <pqxx/pqxx>
#include "MersennePrime.h"

namespace pqxx
{
	template<> struct nullness<pjk::MersennePrime>:no_null<pjk::MersennePrime>{};
	template<> struct string_traits<pjk::MersennePrime>
	{
		const bool converts_to_string{ false };
	    const bool converts_from_string{ false };
	    zview to_buf(char* begin, char* end, pjk::MersennePrime const& value);
		char* into_buf(char* begin, char* end, pjk::MersennePrime const& value);
		std::size_t size_buffer(pjk::MersennePrime const& value) noexcept;
	    pjk::MersennePrime from_string(std::string_view text);
	};

	zview string_traits<pjk::MersennePrime>::to_buf(char* begin, char* end, pjk::MersennePrime const& value)
	{
		return zview();
	}

	char* string_traits<pjk::MersennePrime>::into_buf(char* begin, char* end, pjk::MersennePrime const& value)
	{
	   return nullptr;
	}

	std::size_t string_traits<pjk::MersennePrime>::size_buffer(pjk::MersennePrime const& value) noexcept
	{
		return sizeof(value);
	}
}
#endif

[tt4g]

The function body is not implemented correctly.
It must be implemented so that data exchange occurs in both directions between the application and the database.

And, it is not possible to map all variables in pjk::MersennePrime to a single database column (unless mapping to JSON type).
This means that only some variables of pjk::MersennePrime are registered to the database, and only one of the pjk::MersennePrime variables can be set when retrieved from the database.

Sorry, but I think you know very little about C++ templates.
I think you should design your application to pass pjk::MersennePrime variables to libpqxx functions without forcing them to use custom types.

[pkfox]

You are correct (I think you know very little about C++ templates), I already have my application passing variables to libpqxx functions successfully but would like to know how to use custom types, I'm retired and this is just a hobby application. When you say The function body is not implemented correctly which one are you referring to ?

[tt4g]

to_buf, into_buf and size_buffer is wrong. They do not correctly send A data to the database and do not restore A from the database.
And from_string is missing.

I recommend that you read the following documentation and test code carefully and make sure you understand what each function is doing before working.

• libpqxx: Supporting additional data types
• https://github.com/jtv/libpqxx/blob/7.8.1/test/test_types.hxx#L79-L143

[pkfox]

@tt4g as I told @jtv that stuff is way over my head what I need is a code example showing what is required - once I've seen that I will understand - thanks again

[tt4g]

The test code (https://github.com/jtv/libpqxx/blob/7.8.1/test/test_types.hxx#L79-L143) is an example, and each function is described in the documentation (libpqxx: Supporting additional data types).

If you cannot understand them even after reading them, it is considered that you lack knowledge and experience in the C++ language, so you may want to study C++ first.

[jtv]

With all that, you can pass a prime's attributes to utils.editmersenne as three separate parameters.

[pkfox]

Hi @jtv, glad you are back in business, I have already written all the CRUD code to manage the database. What I want to do is replace this

this->PGTransaction.exec_params("select editmersenne($1,$2,$3)", this->Mersenne, this->MersenneResult, this->PrimeProbabilityText);

with this

this->PGTransaction.exec_params("select editmersenne($1)", this->MP);

where this->MP is a populated instance of a MersennePrime.
I only put a cut down class definition on here for brevity.

[pkfox]

With all that, you can pass a prime's attributes to utils.editmersenne as three separate parameters.
Which is what I currently do

[jtv]

Ah. But that's exactly the unusual part that made it difficult for you. So I have to ask: why do you want that?

[tt4g]

I thought I told you before.
Custom types are APIs for exchanging data with a single column on the database.
If you want to register all attributes of MersennePrime to the database, you need to pass all attributes one by one as @jtv described.

[pkfox]

Ah. But that's exactly the unusual part that made it difficult for you. So I have to ask: why do you want that?

Purely for neatness, I envisage extending this class and don't want to end up passing a load of separate parameters when I could easily package them in a class. I also dislike building dynamic SQL in code. You did ask 😃

[pkfox]

Is it possible to do what I'd like to do ?

[jtv]

Should be, yes. It's just pretty rare and increases interactions between components. (Also means you're more sensitive in the libpqxx string conversion APIs of course.)

To do it, you'd have to go back to defining pqxx::string_traits<MersennePrime> and pqxx::nullness<MersennePrime>. You were on the right track for that earlier.

That in turn does mean learning about C++ template specialisation and SQL compound types. From a software engineering standpoint I would recommend: do the simple thing now, put off the fancy stuff until later, focus on getting your application working, and get back to this issue once higher priorities are done and you may well have become more familiar with the other concepts along the way. But if learning the fancy stuff is the real purpose then I can see why you might want to do this.

[pkfox]

As jeroen asks, if your real purpose is to learn the more advanced C++ techniques then I would recommend that you go somewhere like the include.cpp discord server where there's a lot more people who can help you with the C++ aspects.

@KayEss I only really want to know how it's done in this library - thanks for the link though

[tt4g]

Mapping a structure such as MersennePrime to a single database column requires serializing the structure into a representable format, such as JSON or a message pack, but libpqxx does not provide such an API.
Use another library for serialization or implement the serialization process yourself.

[pkfox]

@tt4g I don't want to map it to a single database column, I merely want to pass an instance of the class as a parameter to a Postgresql function.

[tt4g]

@jtv shows a sample of how to do this in #757 (comment)
Instance variables can only be individually bind to PostgreSQL parameters.

[KayEss]

this->PGTransaction.exec_params("select editmersenne($1)", this->MP);

Can you share your source code for this editmersenne function that only takes one argument with us?

[pkfox]

this->PGTransaction.exec_params("select editmersenne($1)", this->MP);

Can you share your source code for this editmersenne function that only takes one argument with us?

Sure

CREATE OR REPLACE FUNCTION utils.editmersenne(p_mp mersenneprime)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare
RetVal int;
begin
update mersennes
set prime = p_mp.mersenne,
mersenneresult = p_mp.mersenneresult,
amend_date = now(),
numberofdigits = length(trim(p_mp.mersenneresult)),
probability  = p_mp.primeprobabilitytext
where prime = p_mp.mersenne
returning id into RetVal;
return RetVal;
END;
$function$;

[pkfox]

@jtv shows a sample of how to do this in #757 (comment) Instance variables can only be individually bind to PostgreSQL parameters.

No he doesn't, what he shows is how to do it using separate parameters.

[tt4g]

No he doesn't, what he shows is how to do it using separate parameters.

That is correct, but you cannot send structures added (CREATE TYPE) directly on PostgreSQL using the prepared statement.
You can only pass it as separate parameters from C++.

Convert the data sent as separate parameters to utils.mersenneprime in the database.
There is a sample in the PostgreSQL documentation: https://www.postgresql.org/docs/15/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

As far as the definition of util.editmersenne is concerned, if you are sending three MersennePrime variables from C++ as prepaid parameters, the following SQL should be able to convert them to utils.mersenneprime in the database.

SELECT utils.editmersenne(ROW($1, $2, $3)::utils.mersenneprime);

[pkfox]

No he doesn't, what he shows is how to do it using separate parameters.

That is correct, but you cannot send structures added (CREATE TYPE) directly on PostgreSQL using the prepared statement. You can only pass it as separate parameters from C++.

Convert the data sent as separate parameters to utils.mersenneprime in the database. There is a sample in the PostgreSQL documentation: https://www.postgresql.org/docs/15/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

As far as the definition of util.editmersenne is concerned, if you are sending three MersennePrime variables from C++ as prepaid parameters, the following SQL should be able to convert them to utils.mersenneprime in the database.

SELECT utils.editmersenne(ROW($1, $2, $3)::utils.mersenneprime);

I'll give that a shot thanks

[tt4g]

as I've said before that's how I currently do it

I don't know of any other way to do it either.
And you can't send C++ types to PostgreSQL because libpqxx's custom types are not a way to send data structures other than the standard types added on PostgreSQL to the database.

The only other way is to convert the MersennePrime to a data structure such as JSON and create a mechanism to convert it to utils.mersenneprime in the database.

[pkfox]

as I've said before that's how I currently do it

I don't know of any other way to do it either. And you can't send C++ types to PostgreSQL because libpqxx's custom types are not a way to send data structures other than the standard types added on PostgreSQL to the database.

The only other way is to convert the MersennePrime to a data structure such as JSON and create a mechanism to convert it to utils.mersenneprime in the database.

I wonder how npgsql do it in C# ?

this.DataSourceBuilder.MapComposite<MersennePrime>("mersenneprime");

[tt4g]

I don't know about npgsql, but does it send all variables of the structure as one parameter to the database?
Is there documentation that shows what SQL is sent to the database when using that feature?

[pkfox]

@tt4g I'll see if I can find out

[pkfox]

Have a look here
Composite types

[jtv]

as I've said before that's how I currently do it

I don't know of any other way to do it either.
And you can't send C++ types to PostgreSQL because libpqxx's custom types are not a way to send data structures other than the standard types added on PostgreSQL to the database.

The only other way is to convert the MersennePrime to a data structure such as JSON and create a mechanism to convert it to utils.mersenneprime in the database.

Well AFAIK you can represent the object as a string, and the string conversions let you do exactly that (and parse the strings when going the other way).

[tt4g]

Have a look here Composite types

From reading the documentation, it looks like the structure is mapped to a single prepared parameter, but I don't know what data is actually being sent to PostgreSQL.

If we knew what data this library was sending to PostgreSQL, libpqxx might be able to bind the structure to a single parameter.
I'm not an expert in C# and the library is so large and complex that I had no idea what I was doing.

[jtv]

Have a look here
Composite types

Enums are not a great example here - in libpqxx you just call a preprocessor macro and you've got support for your enum. Composite types are a somewhat different story.

[pkfox]

I'm. sure there are people here that know how to do this but it doesn't look like it's going to happen so I might as well close it