after hours of documentations/boards/mailinglists and no progress I may ask you: How do I 'encode' my data to use it for binary transport using libpq's PQexecParams(.)
?
Simple variables are just in big endian order:
PGconn *conn;
PGresult *res;
char *paramValues[1];
int paramLengths[1];
int paramFormats[1];
conn = PQconnectdb(CONNINFO);
// -- (1) -- send a float value
float val_f = 0.12345678901234567890; // float precision: ~7 decimal digits
// alloc some memory & write float (in big endian) into
paramValues[0] = (char *) malloc(sizeof(val_f));
*((uint32_t*) paramValues[0]) = htobe32(*((uint32_t*) &val_f)); // host to big endian
paramLengths[0] = sizeof(val_f);
paramFormats[0] = 1; // binary
res = PQexecParams(conn, "SELECT $1::real ;", //
1, // number parameters
NULL, // let the backend deduce param type
paramValues, //
paramLengths, //
paramFormats, //
0); // return text
printf("sent float: %s \n", PQgetvalue(res, 0, 0));
// --> sent float: 0.123457
and like this also double, int, etc ...
But how about ARRAYs?
float vals_f[] = {1.23, 9.87};
// alloc some memory
paramValues[0] = (char *) malloc(sizeof(float) * 2);
// ???? paramValues[0] = ??????
paramLengths[0] = sizeof(float) * 2;
paramFormats[0] = 1; // binary
res = PQexecParams(conn, "SELECT $1::real[] ;", //
1, // number parameters
NULL, // let the backend deduce param type
paramValues, //
paramLengths, //
paramFormats, //
0); // return text
printf("sent float array: %s \n", PQgetvalue(res, 0, 0));
Is there any working example of transfering ARRAY data in PostgreSQL's binary format?
The code in backend/utils/adt/
doesn't help me much (except I now know there is a ARRAYTYPE, but not how to use them) :-(
I just need a function char* to_PQbin(float [] input, int length)
for passing to paramValues[.]
...
Thanks a lot, Tebas
PS: What is the suggested way of converting simple variables (rather than my htobe32(.)
)?
http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/include/utils/array.h;h=7f7e744cb12bc872f628f90dad99dfdf074eb314;hb=master describes Postgres's binary format for arrays. When using libpq, omit the vl_len_ portion. For example, a an array of 4 integers would look like:
0x00000001 0x00000000 0x00000017 0x00000004 0x00000001 0x00000004 0x00000004 0x00000004 0x00000004
This has OID 1007 (INT4ARRAYOID). The first integer is 1 dimension, the second integer is no NULL bitmap (so none of the array's values are NULL), the third integer is the OID of the elements (23, INT4OID), the fourth integer is how big the first dimension is (4), the fifth integer is the starting index of the first dimension. After that is raw array data, in sequential order, each element prefixed by it's length (4 bytes for each integer).
As ccuter already mentioned, you need to create your own API. The following code extracts a 1-dimensional array of int4
's ignoring any NULL values.
#define INT4OID 23
/*! Structure of array header to determine array type */
struct array_int4 {
int32_t ndim; /* Number of dimensions */
int32_t _ign; /* offset for data, removed by libpq */
Oid elemtype; /* type of element in the array */
/* First dimension */
int32_t size; /* Number of elements */
int32_t index; /* Index of first element */
int32_t first_value; /* Beginning of integer data */
};
static int extract_int4_array (char *raw_array,
int32_t **values,
int *num_values) {
/* Array information header */
struct array_int4 *array = (struct array_int4 *) raw_array;
/* Pointer to traverse int array */
int32_t *p_value = &(array->first_value);
/* int value in host byte order */
int32_t hval;
/* Check if we have a 1-dimensional INT4 array */
if (ntohl(array->ndim) != 1
|| ntohl(array->elemtype) != INT4OID) {
return -1;
}
/* Number of elements including NULLs */
int array_elements = ntohl (array->size);
*num_values = 0;
/* Get size of array */
for (int i=0; i<array_elements; ++i) {
/* Check size to see if this is a NULL value */
hval = ntohl (*p_value);
if (hval != -1) {
++p_value;
(*num_values) += 1;
}
++p_value;
}
*values = malloc (*num_values * sizeof **values);
/* Fill output int array. Skip every other value as it contains the size of
* the element */
*num_values = 0; /* Use num_values as the index of the output array */
p_value = &(array->first_value);
for (int i=0; i<array_elements; ++i) {
/* Check size to see if this is a NULL value */
hval = ntohl (*p_value);
if (hval != -1) {
++p_value;
(*values)[*num_values] = ntohl (*p_value);
(*num_values) += 1;
}
++p_value;
}
return 0;
}
There also appears to be a library named libpqtypes which helps for this kind of conversion.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With