How to convert PGresult to custom datatype using libpq (PostgreSQL)
I am using libpq library in C to access my PostgreSQL database. So when I do res = PQexec (conn, "SELECT point FROM test_point3d"); I don't know how to convert the PGresult I got to my custom data type.
I know I can use the PQgetValue function, but again I don't know how to convert the returned string to my custom data type.
a source to share
The best way to think about what data types interact with applications is over text-based interfaces. Libpq returns a string from anything. The programmer is responsible for parsing the string and creating a data type from it. I know the author has probably abandoned the question, but I am working on something similar and it is worth noting a few important tricks that may be useful in some cases.
Obviously, if it is a type of C language, with its own representation inside and out, then you will have to parse the string as usual.
However, for arrays and tuples, the notation is basically
[open_type_identifier][csv_string][close_type_identifier]
For example, a tuple can be represented as:
(35,65,1111111,f,f,2011-10-06,"2011-10-07 13:11:24.324195",186,chris,f,,,,f)
This simplifies the analysis. You can usually use existing csv handlers after disabling the first and last characters. Moreover, consider:
select row('test', 'testing, inc', array['test', 'testing, inc']);
row
-------------------------------------------------
(test,"testing, inc","{test,""testing, inc""}")
(1 row)
As shown in the picture, you have standard CSV escaping inside nested attributes, so you can actually determine that the third attribute is an array, and then (with double quotes) parse it as an array. This way, nested data structures can be handled in much the same way as you might expect in JSON format. The trick is that it is nested by CSV.
a source to share