PostgreSQL's libpq: Encoding for binary transport of ARRAY[]-data?

c, c++, libpq, postgresql

Solution

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.

Problem

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(.)`)?

Original source