10

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?

d11wtq
  • 34,788
  • 19
  • 120
  • 195

2 Answers2

15

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.

d11wtq
  • 34,788
  • 19
  • 120
  • 195
  • 5
    These headers are part of the postgres server code rather than the libpq client code. Do the values change between postgres releases? – KayEss Dec 19 '15 at 08:45
4

To get the type name from an OID, just cast it to regtype:

SELECT  700::oid::regtype -- real

To get the type of any columns (or variable in plpgsql), use pg_typeof():

SELECT  pg_typeof(1::real) -- real

Gives you an answer of type regtype which is displayed as text in psql or pgAdmin. You can cast it to text explicitly if needed:

SELECT  pg_typeof(1::real)::text -- real

There is also this "big list", vulgo catalog table pg_type, where types are registered. This can be big, have a peek:

SELECT * from pg_type LIMIT 10;

More info in the excellent manual.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    Thanks, though do you know if there's a way to get this from the C API instead of round-tripping to the server for every column in every result? I'm only interested in core types, not user types. – d11wtq Sep 21 '12 at 12:38
  • You can have Postgres send this information with every result, just add SELECT items - though it would be redundant (per row). I am not familiar with the C API. – Erwin Brandstetter Sep 21 '12 at 12:40
  • No dramas. Thanks for your help. I'm implementing a client library, so would prefer not to modify user queries, or send excessive additional requests to the server. I think I have found it now, actually, in catalog/pg_type.h :) – d11wtq Sep 21 '12 at 12:42
  • @d11wtq: Cool. No surprise abut the name, huh? :) – Erwin Brandstetter Sep 21 '12 at 12:43