在C中使用Postgresql插入整数数组（libpq） [英] Inserting integer array with postgresql in C (libpq)

问题描述

我正在尝试将整数数组发布到我的postgresql数据库中。我知道我可以将所有内容格式化为字符串，然后将该字符串作为一个SQL命令发送。但是，我相信PQexecParams函数也应该带来一些帮助。但是，我对如何使用它迷失了。

I'm trying to post an integer array into my postgresql database. I'm aware that I could format everything as a string and then send that string as one SQL command. However, I believe the PQexecParams function should also bring some help. However, I'm kind of lost as how to use it.

    //we need to convert the number into network byte order
    int val1 = 131;
    int val2 = 2342;
    int val3[5] = { 0, 7, 15, 31, 63 };
    //set the values to use
    const char *values[3] = { (char *) &val1, (char *) &val2, (char *) val3 };
    //calculate the lengths of each of the values
    int lengths[3] = { sizeof(val1), sizeof(val2), sizeof(val3) * 5 };
    //state which parameters are binary
    int binary[3] = { 1, 1, 1 };

    PGresult *res = PQexecParams(conn, "INSERT INTO family VALUES($1::int4, $2::int4, $3::INTEGER[])", 3, //number of parameters
            NULL, //ignore the Oid field
            values, //values to substitute $1 and $2
            lengths, //the lengths, in bytes, of each of the parameter values
            binary, //whether the values are binary or not
            0); //we want the result in text format

是的，这是从某些教程中复制的。
但是，这将返回：

Yes this is copied from some tutorial. However this returns :

 ERROR:  invalid array flags

使用常规方法确实有效：

Using a conventional method does work:

PQexec(conn, "INSERT INTO family VALUES (2432, 31, '{0,1,2,3,4,5}')");

插入数据很好，我也可以读出。

Inserts data just fine, and I can read it out fine as well.

任何帮助将不胜感激！ ：）

Any help would be greatly appreciated! :)

推荐答案

libpq 的 PQexecParams 可以接受文本或二进制形式的值。

libpq's PQexecParams can accept values in text or binary form.

对于文本值，您必须 sprintf 将整数放入您放入 char ** 值数组的缓冲区中。通常就是这样。您可以使用带有查询参数的文本格式，没有特别的理由自己退后将参数插值到SQL字符串中。

For text values, you must sprintf the integer into a buffer that you put in your char** values array. This is usually how it's done. You can use text format with query parameters, there is no particular reason to fall back to interpolating the parameters into the SQL string yourself.

如果要使用二进制模式传输，则必须确保整数是目标字段的正确大小，并且是网络字节顺序，并且已指定OID类型。使用 htonl （用于 uint32_t ）或 htons （用于 uint16_t ）。取消签名是很好的，因为您只是在重新排列字节。

If you want to use binary mode transfers, you must instead ensure the integer is the correct size for the target field, is in network byte order, and that you have specified the type OID. Use htonl (for uint32_t) or htons (for uint16_t) for that. It's fine to cast away signedness since you're just re-ordering the bytes.

因此：

• 如果您打算使用二进制传输，则不能忽略OID字段


• 使用 htonl ，不要酿造自己的字节顺序转换


• 您的值数组构造错误。您要将 char ** s放入 char * 数组中，并丢弃了错误的类型。您要& val1 [0] 或（在大多数/所有实际C实现中等效，但在技术上与规范不相同）只是 val1 ，而不是（char *）& val1


• 您不能假定在线 integer [] 的格式与C的 int32_t [] 相同。您必须传递OID类型 INT4ARRAYOID （请参见 include / catalog / pg_type.h 或从pg_type中选择oid，其中typname ='_int4'-数组的内部类型名称位于其前面的 _ 基本类型）和必须构造与 pg_type 中的 typreceive 函数兼容的PostgreSQL数组值如果您打算以二进制模式发送，则使用该类型（ array_recv ）。特别是，二进制格式的数组具有标头。您不能只留下标题。

• You cannot ignore the OID field if you're planning to use binary transfer
• Use htonl, don't brew your own byte-order conversion
• Your values array construction is wrong. You're putting char**s into an array of char* and casting away the wrong type. You want &val1[0] or (equivalent in most/all real-world C implementations, but not technically the same per the spec) just val1, instead of (char*)&val1
• You cannot assume that the on-wire format of integer[] is the same as C's int32_t[]. You must pass the type OID INT4ARRAYOID (see include/catalog/pg_type.h or select oid from pg_type where typname = '_int4' - the internal type name of an array is _ in front of its base type) and must construct a PostgreSQL array value compatible with the typreceive function in pg_type for that type (which is array_recv) if you intend to send in binary mode. In particular, binary-format arrays have a header. You cannot just leave out the header.

换句话说，代码以多种令人兴奋的方式被破坏，并且可能无法按编写的方式工作。

In other words, the code is broken in multiple exciting ways and cannot possibly work as written.

真的，以二进制模式发送整数几乎没有任何好处。实际上，以文本模式发送通常会更快，因为它通常更紧凑（较小的值）。如果要使用二进制模式，则需要了解C如何表示整数，网络与主机字节顺序的工作方式等。

Really, there is rarely any benefit in sending integers in binary mode. Sending in text-mode is often actually faster because it's often more compact on the wire (small values). If you're going to use binary mode, you will need to understand how C represents integers, how network vs host byte order works, etc.

尤其是在处理数组时，文本格式更容易。

Especially when working with arrays, text format is easier.

libpq 可以通过提供良好的数组构造使此操作比目前容易得多/解构文本和二进制数组的函数。补丁一如既往地受欢迎。现在，第三方库，例如 libpqtypes 角色。

libpq could make this a lot easier than it presently does by offering good array construct / deconstruct functions for both text and binary arrays. Patches are, as always, welcome. Right now, 3rd party libraries like libpqtypes largely fill this role.

这篇关于在C中使用Postgresql插入整数数组（libpq）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！