I have a question regarding the return value of PQfformat()
I have a 'data' column in my table, type bytea (postgresql 9.1.5).
In postgresql.conf: bytea_output = 'escape'
When I execute the query: PGresult *res = PQexec(db, "SELECT data::bytea FROM data_table WHERE id='xxx'")
PQexec() always returns data in the text format. You should use PQexecParams() to obtain the data as binary.
And I run through the results:
int i, j; for (i = 0; i < PQntuples(res); i++) { for (j = 0; j < PQnfields(res); j++) { printf("Format %d: %d\n", j, PQfformat(res, j)); printf("Type %d: %d\n", j, PQftype(res, j)); } }
This prints that the format is type 0, and the type is 17.
Shouldn't the format be 1 (binary data)?
I am getting a discrepancy between data that I put into the table and data I retrieve. When I dump the data, using:
int di; char *val = PQgetvalue(res, i, j); for (di = 0; di < 16; di++) fprintf(stderr, "%2x ", val[di]);
I see the following: 30 5c 33 33 32 5c 30 30 30 5c 30 31 31 5c 30 30
But when I look at the same data in the database:
psql> select encode(substr(data, 0, 16), 'hex') from data_table where id='xxx'; encode -------------------------------- 30da00090132420520203137323030
This is the data I'm expecting to get back. Is the '00' (third byte) causing the problem?
The data looks the same at a certain place (ie it starts with the same byte 30, then the C code has 22 bytes whereas the db hex dump has 7 bytes, then the data is the same again. The 7/22 number of bytes isn't always the same, across the different data values). -- Jason Armstrong
