Get (integer[]) array value from the query result of PostgreSQL using libpq library

Question

after executing query I run below functions but don't know how to convert value to c integer array.

how to get integer array from pptRawValue where COLUMN_VALUE_IN_BINARY_FORMAT when I looked at memory i can see the value are present in pptRawValue but not able to get that values in integer array in c code?

please help.

long lColumnNum = PQfnumber(ptQueryRes, pstrColumnName);
long lFormat = PQfformat(ptQueryRes, lColumnNum);
*pptRawValue = PQgetvalue(ptQueryRes, lRowNum, lColumnNum);
*plValueLength = PQgetlength(ptQueryRes, lRowNum, lColumnNum);
int iarray[];

"`integer`" is not a C data type (though the term is used for a *category* of data types). Do you mean `int`? — John Bollinger, Dec 12 '17 at 14:09

Laurenz Albe · Answer 1 · 2017-12-13T15:10:26.377

0

You can either request text mode output, then an integer[] would look like {1,2,42}, or you can request binary mode, but then you'd have to deal with the internal array representation of the PostgreSQL server.

About binary mode for integer[]:

Integers themselves are represented in network byte order, so endianness shouldn't be a problem.

You'll have to read the PostgreSQL source to understand the structure of an ArrayType.

Binary more could be a problem if client and server have a different architecture, because the alignment might differ.

edited Dec 13 '17 at 15:10

answered Dec 12 '17 at 14:23

Laurenz Albe

209,280
17
206
263

yes but currently I cannot convert it to text as it is requirement but i can use ntohl, ntohs, – MUKHTAR INAMDAR Dec 13 '17 at 14:09
That's no problem (I have expanded the answer). The internal structure of the array is the problem. – Laurenz Albe Dec 13 '17 at 15:10
yes I read about internal format however first I am checking if the response in binary format if yes i am going ahead. Thanks for your help. – MUKHTAR INAMDAR Dec 20 '17 at 13:12
going forward the text option will available to me so it will be more save. Thanks for alert. – MUKHTAR INAMDAR Dec 20 '17 at 13:14

MUKHTAR INAMDAR · Accepted Answer · 2017-12-20T13:23:54.513

Found the way to extract values from received binary array

The received binary array has below format. I have actually verified it.

This didn't quite agree with what I found but it gave me a starting point. Here is what I found and hope experts will correct me if I am wrong:

The first 20 bytes seem to have the following information about the array:

--first 4 bytes don't know what it is but it is always 1
This is may be format number as Laurenz Albe mentioned above.
Reference:https://www.postgresql.org/docs/current/static/protocol-overview.html#PROTOCOL-FORMAT-CODES
--second 4 bytes " " " " " " " " " 0
--third 4 bytes oid of the datatype in the array
--4th 4 bytes number of elements in the array
--5th 4 bytes dimension of the array

Form here we have the actual data. Each datum is preceeded with a 4 byte integer indicating the number of bytes the next element occupies. Knowing the number of elements one can parse the memory and get access to the elements.

Please fallow the link: https://www.postgresql.org/message-id/attachment/13504/arrayAccess.txt

Get (integer[]) array value from the query result of PostgreSQL using libpq library

please help.

2 Answers2

Found the way to extract values from received binary array