I'm working with the PostgreSQL C API, libpq. I need to be able to convert the values in a PGresult*
into their equivalent data types in Ruby. I'm currently just selecting all the data and using PQgetvalue()
, which gives me a char*
that I can convert into a ruby String. That's easy. But are there any examples somebody can share that do a type conversion from the char*
to, say, int
, float
or double
, according to the OID returned by PQftype()
?
Actually, in short I have no idea how to interpret the OID and the documentation doesn't seem to give any pointers. I found this page, but that doesn't help understand how to use this OID to do a type conversion in the C API. I'm guessing there's a list of constants somewhere I can make a big switch statement from?
I found the answer after asking this. Basically there's a file called catalog/pg_type.h, alongside libpq-fe.h and postgres.h. You need to include after including libpq-fe.h and postgres.h, then you can access the definitions like TEXTOID
, BOOLOID
, INT4OID
etc.
#include <stdio.h>
#include <postgres.h>
#include <libpq-fe.h>
#include <catalog/pg_type.h>
// ... snip ...
if (PQgetisnull(result, row, col)) {
// value is NULL, nothing more to do
} else {
char * value = PQgetvalue(result, row, col);
int length = PQgetlength(result, row, col);
switch (PQftype(result, col)) {
case INT2OID:
case INT4OID:
case INT8OID:
// process value as an integer
break;
default:
// just default to a text representation
}
}
You need to look at all the OIDs in pg_type.h to actually have an extensive list, or just test what you get back doing basic SELECT 't'::boolean
type queries etc and build up the switch only as you need a new type supporting.