How to convert a PGresult to custom data type with libpq (PostgreSQL)

I'm using the libpq library in C to accessing 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 returning string to my custom data type.

The best way to think about this is that data types interact with applications over a textual interfaces. Libpq returns a string from just about anything. The programmer has a responsibility to parse the string and create 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 documenting a few important tricks here that are helpful in some cases.

Obviously if this is a C language type, with its own in and out representation, then you will have to parse the string the way you would normally.

However for arrays and tuples, the notation is basically

[open_type_identifier][csv_string][close_type_identifier]

For example a tuple may be represented as:

(35,65,1111111,f,f,2011-10-06,"2011-10-07 13:11:24.324195",186,chris,f,,,,f)

This makes it easy to parse. You can generally use existing csv processers once you trip off the first and last character. Moreover, consider:

select row('test', 'testing, inc', array['test', 'testing, inc']);
                       row                       
-------------------------------------------------
 (test,"testing, inc","{test,""testing, inc""}")
(1 row)

As this shows you have standard CSV escaping inside nested attributes, so you can, in fact, determine that the third attribute is an array, and then (having undoubled the quotes), parse it as an array. In this way nested data structures can be processed in a manner roughly similar to what you might expect with a format like JSON. The trick though is that it is nested CSV.