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 this routine 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 but it 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 following commands
* (provided in src/test/examples/testlibpq3.sql):
*
* CREATE TABLE test1 (i int4, t text, b bytea);
*
* INSERT INTO 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. We split 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 local byte order.
*/
ival = ntohl(*((uint32_t *) iptr));

/*
* The binary representation of TEXT is, well, text, and since libpq
* was nice enough to append a zero byte to it, it'll work just fine
* as a C string.
*
* The binary representation of BYTEA is a bunch 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 to setting 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 as binary transmission of data.
*
* This first example transmits the parameters as text, but receives the
* results in binary format. By using out-of-line parameters we can
* avoid a lot of tedious mucking about with quoting and escaping, even
* though the data is text. Notice how we don't have to do anything
* special with the quote mark in 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, /* one param */
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 in binary form.
*
* Although we tell PQexecParams we are letting the backend deduce
* parameter type, we really force the 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;
}