Re: PQfformat question and retrieving bytea data in C

On Wed, Aug 29, 2012 at 8:05 AM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com> wrote:
> Hey Jason,
>
> 2012/8/29 Jason Armstrong <ja(at)riverdrums(dot)com>
>>
>> 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.

Also see libpqtypes. It abstracts you from the wire format and
returns data in a regular way:

int success;
PGint4 i4;
PGtext text;
PGbytea bytea;
PGpoint pt;
PGresult *res = PQexec(conn, "SELECT i,t,b,p FROM tbl");

/* Get some field values from the result (order doesn't matter) */
success = PQgetf(res,
0, /* get field values from tuple 0 */
"%int4 #text %bytea %point",
/* type format specifiers (get text by name '#') */
0, &i4, /* get an int4 from field num 0 */
"t", &text, /* get a text from field name "t" */
2, &bytea, /* get a bytea from field num 2 */
3, &pt); /* get a point from field num 3 */

/* Output an error message using PQgeterror(3). */
if(!success)
fprintf(stderr, "*ERROR: %s\n", PQgeterror());

/* Output the values, do this before PQclear() */
else
printf("int4=%d, text=%s, bytea=%d bytes, point=(%f,%f)\n",
i4, text, bytea.len, pt.x, pt.y);

PQclear(res);

merlin