如何使用libpq以二进制格式在PostgreSQL表中插入文本数组？ [英] How to insert text array in PostgreSQL table in binary format using libpq?

问题描述

我没有找到任何文档描述如何使用libpq在PostgreSQL中使用二进制数组。
因此，您有了表：

I didn't find any documentation describing how to work with binary arrays in PostgreSQL using libpq. So you have table:

CREATE TABLE IF NOT EXISTS test_array ( array_column text[] )

您要使用 PQexecParams $ b $以二进制格式插入数组b这到底是什么呢？

You want to insert array in binary format using PQexecParams How exactly one does this?

推荐答案

下面介绍的所有内容都在PostgreSQL 9.5上进行了测试。

Everything described bellow is tested on PostgreSQL 9.5.

PQexecParams具有以下原型：

PQexecParams has the following prototype:

PGresult *PQexecParams(PGconn *conn,
                       const char *command,
                       int nParams,
                       const Oid *paramTypes,
                       const char * const *paramValues,
                       const int *paramLengths,
                       const int *paramFormats,
                       int resultFormat);

https://www.postgresql.org/docs/current/static/libpq-exec.html

Oid 可以在

/ postgresql_sources_folder / src / include / catalog / pg_type.h

/postgresql_sources_folder/src/include/catalog/pg_type.h

在我们的例子中，我们需要其中两个：

In our case we need 2 of them:

#define TEXTARRAYOID        1009
#define TEXTOID         25

外部PostgreSQL二进制数组的确切格式可以从函数中推导出来：

The exact format of external PostgreSQL binary arrays can be deduced from function:

Datum array_send(PG_FUNCTION_ARGS);

在这里定义：

/postgresql_sources_folder/src/backend/utils/adt/arrayfuncs.c

/postgresql_sources_folder/src/backend/utils/adt/arrayfuncs.c

我用结构描述格式：

struct pgValsT {
  /* number of array dimensions */
  int32_t ndims;

  /* flag describing if array has NULL values */
  int32_t hasNull;

  /* Oid of data stored in array. In our case is 25 for TEXT */
  Oid oidType;

  /* Number of elements in array */
  int32_t totalLen;

  /* Not sure for this one. 
     I think it describes dimensions of elements in case of arrays storing arrays */
  int32_t subDims;

  /* Here our data begins */
  char dataBegin[];
}__attribute__ ((__packed__));

数组元素存储为结构：

struct varlena
{
  /* -1 if data = NULL */
  int32_t vl_len;

  /* our data */
  char vl_dat[];
}__attribute__ ((__packed__));

相同的格式可用于检索二进制格式的数组。

Same can be applied for retrieving arrays in binary format.

以二进制格式将文本数组插入问题表的完整示例：

Full example for insertion text array in binary format to table described in question:

#include <postgresql/libpq-fe.h>
#include <arpa/inet.h>
#include <cstdlib>
#include <cstring>

#define TEXTOID         25
#define TEXTARRAYOID        1009


int main(){

  PGconn* conn = PQsetdbLogin("localhost", "5432",
                 nullptr, nullptr, "my_database", "my_user",
                  "my_password");

  PGresult* res = NULL;

  /* our strings to pass as array. Sizes should exclude terminating '\0' 
     and be presented in network byte order */

  const char aStr1[] = "Stackoverflow";
  uint32_t pgSizeStr1 = ntohl(sizeof(aStr1) - 1);

  const char aStr2[] = "is";
  uint32_t pgSizeStr2 = ntohl(sizeof(aStr2) - 1);

  const char aStr3[] = "awesome!";
  uint32_t pgSizeStr3 = ntohl(sizeof(aStr3) - 1);

  /* number of parameters for PQexecParams. We need 1 for sending 1 text array */
  int nParams = 1;

  /* number of elements in array */
  int nElems = 3;

  /* our type is text array */
  Oid paramTypes[] = { TEXTARRAYOID };

  /* Allocating memory for our pgValsT structure + our data. Don't allocate memory for '\0' at the end of a string */

  uint32_t dataSize = sizeof(pgValsT) + sizeof(int32_t) * nElems + sizeof(aStr1) + sizeof(aStr2) + sizeof(aStr3) - nElems;
  void* vData = malloc(dataSize);

  pgValsT* pDataStruct = (pgValsT*)vData;

  /* setting up pointer to data to send to PostgreSQL */
  char* paramValues[] = { (char*)vData };

  /* setting up our element size */
  int paramLengths[] = { dataSize };

  /* setting binary format for our data */
  int paramFormats[] = { 1 };

  /* our array has one dimension */
  pDataStruct->ndims = ntohl(1);

  /* our array has no NULL elements */
  pDataStruct->hasNull = ntohl(0);

  /* type of our elements is text */
  pDataStruct->oidType = ntohl(TEXTOID);

  /* our array has 3 elements */
  pDataStruct->totalLen = ntohl(nElems);

  pDataStruct->subDims = ntohl(1);


  /* copy our strings and sizes to data structure excluding terminating '\0' */
  size_t byteOffset = 0;
  memcpy(pDataStruct->dataBegin, &pgSizeStr1, sizeof(pgSizeStr1));
  memcpy(pDataStruct->dataBegin + sizeof(pgSizeStr1), aStr1, sizeof(aStr1) - 1);
  byteOffset += sizeof(pgSizeStr1) + sizeof(aStr1) - 1;

  memcpy(pDataStruct->dataBegin + byteOffset, &pgSizeStr2, sizeof(pgSizeStr2));
  memcpy(pDataStruct->dataBegin + byteOffset + sizeof(pgSizeStr2), aStr2, sizeof(aStr2) - 1);
  byteOffset += sizeof(pgSizeStr2) + sizeof(aStr2) - 1;


  memcpy(pDataStruct->dataBegin + byteOffset, &pgSizeStr3, sizeof(pgSizeStr3));
  memcpy(pDataStruct->dataBegin + byteOffset + sizeof(pgSizeStr3), aStr3, sizeof(aStr3) - 1);

  /* executing query */
  res = PQexecParams(conn, "INSERT INTO test_array (array_column) values ($1)", 1, paramTypes, paramValues, paramLengths, paramFormats, 1);

  PQfinish(conn);
  PQclear(res);
  free(vData);
}

这篇关于如何使用libpq以二进制格式在PostgreSQL表中插入文本数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！