Re: How do you convert PostgreSQL internal binary field to C datatypes

On Wed, 2007-05-23 at 11:47 -0400, Jeff Lynn wrote:
> I give up!  What ever data returned from the binary output of 
> PGgetvalue() is, I don't know how to convert it into StringInfo, which 
> is being used by a few routines that "seems" to convert internal binary 
> format into float8.  By casting the returned data into StringInfo, 
> crashes the function.
> 
> If PostgreSQL  support only a half baked C API, may as well don't bother.
> 
> So I gave up.   I will not use PostgreSQL until there is a simple way to 
> extract data directly into C or C++ data type.  Is one expect to fetch 
> data in text, then use text to int, text to double, text to date 
> function before you can make any business logic works?
> 
> Thanks all of you trying to help.  Appreciated!
> 
> Jeff
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq

I would think that using the info provided at these links should enable
what you wish....

LINK 1:

http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/pqformat.c?rev=1.45;content-type=text%2Fplain

/* --------------------------------*        pq_sendfloat4    - append a float4 to a StringInfo buffer** The point of
thisroutine is to localize knowledge of the external binary* representation of float4, which is a component of several
datatypes.**We currently assume that float4 should be byte-swapped in the same way* as int4.  This rule is not perfect
butit gives us portability across* most IEEE-float-using architectures.* --------------------------------*/
 
void
pq_sendfloat4(StringInfo buf, float4 f)
{union{    float4        f;    uint32        i;}            swap;
swap.f = f;swap.i = htonl(swap.i);
appendBinaryStringInfo(buf, (char *) &swap.i, 4);
}


LINK2:


http://www.postgresql.org/docs/8.2/interactive/libpq-example.html

Example 29-3. libpq Example Program 3

/** testlibpq3.c*      Test out-of-line parameters and binary I/O.** Before running this, populate a database with the
followingcommands* (provided in src/test/examples/testlibpq3.sql):** CREATE TABLE test1 (i int4, t text, b bytea);**
INSERTINTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');* INSERT INTO test1 values (2, 'ho there',
'\\004\\003\\002\\001\\000');**The expected output is:** tuple 0: got*  i = (4 bytes) 1*  t = (11 bytes) 'joe's place'*
b = (5 bytes) \000\001\002\003\004** tuple 0: got*  i = (4 bytes) 2*  t = (8 bytes) 'ho there'*  b = (5 bytes)
\004\003\002\001\000*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"

/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>


static void
exit_nicely(PGconn *conn)
{   PQfinish(conn);   exit(1);
}

/** This function prints a query result that is a binary-format fetch from* a table defined as in the comment above.
Wesplit it out because the* main() function uses it twice.*/
 
static void
show_binary_results(PGresult *res)
{   int         i,               j;   int         i_fnum,               t_fnum,               b_fnum;
   /* Use PQfnumber to avoid assumptions about field order in result */   i_fnum = PQfnumber(res, "i");   t_fnum =
PQfnumber(res,"t");   b_fnum = PQfnumber(res, "b");
 
   for (i = 0; i < PQntuples(res); i++)   {       char       *iptr;       char       *tptr;       char       *bptr;
 int         blen;       int         ival;
 
       /* Get the field values (we ignore possibility they are null!) */       iptr = PQgetvalue(res, i, i_fnum);
tptr= PQgetvalue(res, i, t_fnum);       bptr = PQgetvalue(res, i, b_fnum);
 
       /*        * The binary representation of INT4 is in network byte order, which        * we'd better coerce to the
localbyte order.        */       ival = ntohl(*((uint32_t *) iptr));
 
       /*        * The binary representation of TEXT is, well, text, and since libpq        * was nice enough to append
azero byte to it, it'll work just fine        * as a C string.        *        * The binary representation of BYTEA is
abunch of bytes, which could        * include embedded nulls so we have to pay attention to field length.        */
 blen = PQgetlength(res, i, b_fnum);
 
       printf("tuple %d: got\n", i);       printf(" i = (%d bytes) %d\n",              PQgetlength(res, i, i_fnum),
ival);      printf(" t = (%d bytes) '%s'\n",              PQgetlength(res, i, t_fnum), tptr);       printf(" b = (%d
bytes)", blen);       for (j = 0; j < blen; j++)           printf("\\%03o", bptr[j]);       printf("\n\n");   }
 
}

int
main(int argc, char **argv)
{   const char *conninfo;   PGconn     *conn;   PGresult   *res;   const char *paramValues[1];   int
paramLengths[1];  int         paramFormats[1];   uint32_t    binaryIntVal;
 
   /*    * If the user supplies a parameter on the command line, use it as the    * conninfo string; otherwise default
tosetting dbname=postgres and using    * environment variables or defaults for all other connection parameters.    */
if(argc > 1)       conninfo = argv[1];   else       conninfo = "dbname = postgres";
 
   /* Make a connection to the database */   conn = PQconnectdb(conninfo);
   /* Check to see that the backend connection was successfully made */   if (PQstatus(conn) != CONNECTION_OK)   {
fprintf(stderr, "Connection to database failed: %s",               PQerrorMessage(conn));       exit_nicely(conn);   }
 
   /*    * The point of this program is to illustrate use of PQexecParams() with    * out-of-line parameters, as well
asbinary transmission of data.    *    * This first example transmits the parameters as text, but receives the    *
resultsin binary format.  By using out-of-line parameters we can    * avoid a lot of tedious mucking about with quoting
andescaping, even    * though the data is text.  Notice how we don't have to do anything    * special with the quote
markin the parameter value.    */
 
   /* Here is our out-of-line parameter value */   paramValues[0] = "joe's place";
   res = PQexecParams(conn,                      "SELECT * FROM test1 WHERE t = $1",                      1,       /*
oneparam */                      NULL,    /* let the backend deduce param type */                      paramValues,
                NULL,    /* don't need param lengths since text */                      NULL,    /* default to all text
params*/                      1);      /* ask for binary results */
 
   if (PQresultStatus(res) != PGRES_TUPLES_OK)   {       fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
  PQclear(res);       exit_nicely(conn);   }
 
   show_binary_results(res);
   PQclear(res);
   /*    * In this second example we transmit an integer parameter in binary    * form, and again retrieve the results
inbinary form.    *    * Although we tell PQexecParams we are letting the backend deduce    * parameter type, we really
forcethe decision by casting the parameter    * symbol in the query text.  This is a good safety measure when sending
* binary parameters.    */
 
   /* Convert integer value "2" to network byte order */   binaryIntVal = htonl((uint32_t) 2);
   /* Set up parameter arrays for PQexecParams */   paramValues[0] = (char *) &binaryIntVal;   paramLengths[0] =
sizeof(binaryIntVal);  paramFormats[0] = 1;        /* binary */
 
   res = PQexecParams(conn,                      "SELECT * FROM test1 WHERE i = $1::int4",                      1,
/* one param */                      NULL,    /* let the backend deduce param type */                      paramValues,
                    paramLengths,                      paramFormats,                      1);      /* ask for binary
results*/
 
   if (PQresultStatus(res) != PGRES_TUPLES_OK)   {       fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
  PQclear(res);       exit_nicely(conn);   }
 
   show_binary_results(res);
   PQclear(res);
   /* close the connection to the database and cleanup */   PQfinish(conn);
   return 0;
}