Thread: [GENERAL] Results interpretation

 Hi,
I looked at the documentation, but couldn't find it.

If I do PQexec() call, the results will be interpreted as binary or text?

I'm trying to get an int field from the query and wonder if I need to do
hton() call or not?

Thank you.

Hi, Vincenzo,

On Fri, Aug 18, 2017 at 1:51 PM, Vincenzo Romano
<vincenzo.romano@notorand.it> wrote:
> Afaik, pgresult structure and it's fields are not meant to be handled
> directly apart of comparing the pointer to null.

So if I want to get an integer value with PQgetValue() I don't need to
do anything?
I.e. no ntoh() call?

Thank you.

>
>
>
> --
> Vincenzo Romano - NotOrAnd.IT
> Information Technologies
> --
> NON QVIETIS MARIBVS NAVTA PERITVS
>
> Il 18 ago 2017 19:46, "Igor Korot" <ikorot01@gmail.com> ha scritto:
>>
>>  Hi,
>> I looked at the documentation, but couldn't find it.
>>
>> If I do PQexec() call, the results will be interpreted as binary or text?
>>
>> I'm trying to get an int field from the query and wonder if I need to do
>> hton() call or not?
>>
>> Thank you.
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general

Hi, Vincenzo,

On Fri, Aug 18, 2017 at 2:52 PM, Vincenzo Romano
<vincenzo.romano@notorand.it> wrote:
> What I can tell you is not more of what is in the documentation.
>
> https://www.postgresql.org/docs/9.6/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO
>
>
> In particular see the function PQBinaryTuples.

OK.
I was looking at PQexec() vs. PQexecParam(), where the latter says:

[quote]
.... and query results can be requested in either text or binary format. ....
[/quote]

and the former doesn't mention anything like this.

Thank you.

>
> --
> Vincenzo Romano - NotOrAnd.IT
> Information Technologies
> --
> NON QVIETIS MARIBVS NAVTA PERITVS
>
> Il 18 ago 2017 20:45, "Igor Korot" <ikorot01@gmail.com> ha scritto:
>>
>> Hi, Vincenzo,
>>
>> On Fri, Aug 18, 2017 at 1:51 PM, Vincenzo Romano
>> <vincenzo.romano@notorand.it> wrote:
>> > Afaik, pgresult structure and it's fields are not meant to be handled
>> > directly apart of comparing the pointer to null.
>>
>> So if I want to get an integer value with PQgetValue() I don't need to
>> do anything?
>> I.e. no ntoh() call?
>>
>> Thank you.
>>
>> >
>> >
>> >
>> > --
>> > Vincenzo Romano - NotOrAnd.IT
>> > Information Technologies
>> > --
>> > NON QVIETIS MARIBVS NAVTA PERITVS
>> >
>> > Il 18 ago 2017 19:46, "Igor Korot" <ikorot01@gmail.com> ha scritto:
>> >>
>> >>  Hi,
>> >> I looked at the documentation, but couldn't find it.
>> >>
>> >> If I do PQexec() call, the results will be interpreted as binary or
>> >> text?
>> >>
>> >> I'm trying to get an int field from the query and wonder if I need to
>> >> do
>> >> hton() call or not?
>> >>
>> >> Thank you.
>> >>
>> >>
>> >> --
>> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-general

    Igor Korot wrote:

> If I do PQexec() call, the results will be interpreted as binary or text?
>
> I'm trying to get an int field from the query and wonder if I need to do
> hton() call or not?

In the most general case, you may call
PQfformat(const PGresult *res, int column_number)
to know if a column is in text (=0) or binary format (=1)

If you call PQexec("select 1") the result will be in text format.

But if you'd write for instance:
 PQexec("begin; declare c binary cursor for select 1; fetch all from c;
end;")
then the result would be in binary format.

The point is that using PQexec() does not strictly mean that the results
are in text, as it depends on the query itself. This might be
significant if there's a requirement that your code has to work
with any query.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Hi, Daniel,

On Sat, Aug 19, 2017 at 12:51 PM, Daniel Verite <daniel@manitou-mail.org> wrote:
>         Igor Korot wrote:
>
>> If I do PQexec() call, the results will be interpreted as binary or text?
>>
>> I'm trying to get an int field from the query and wonder if I need to do
>> hton() call or not?
>
> In the most general case, you may call
> PQfformat(const PGresult *res, int column_number)
> to know if a column is in text (=0) or binary format (=1)
>
> If you call PQexec("select 1") the result will be in text format.
>
> But if you'd write for instance:
>  PQexec("begin; declare c binary cursor for select 1; fetch all from c;
> end;")
> then the result would be in binary format.
>
> The point is that using PQexec() does not strictly mean that the results
> are in text, as it depends on the query itself. This might be
> significant if there's a requirement that your code has to work
> with any query.

Thank you for an explanation.
It would be nice if the documentation will explicitly state:

"The result set mode (text or binary) depends on the query being executed".

In my case I simply executing:

SELECT t.table_catalog AS catalog, t.table_schema AS schema,
t.table_name AS table, u.usename AS owner, c.oid AS table_id FROM
information_schema.tables t, pg_catalog.pg_class c, pg_catalog.pg_user
u WHERE t.table_name = c.relname AND c.relowner = usesysid AND
(t.table_type = 'BASE TABLE' OR t.table_type = 'VIEW' OR t.table_type
= 'LOCAL TEMPORARY') ORDER BY table_name;

So I presume the result set will be in a text format, right? For all columns?

Thank you.

>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite

    Igor Korot wrote:

> In my case I simply executing:
>
> SELECT t.table_catalog AS catalog, t.table_schema AS schema,
> t.table_name AS table, u.usename AS owner, c.oid AS table_id FROM
> information_schema.tables t, pg_catalog.pg_class c, pg_catalog.pg_user
> u WHERE t.table_name = c.relname AND c.relowner = usesysid AND
> (t.table_type = 'BASE TABLE' OR t.table_type = 'VIEW' OR t.table_type
> = 'LOCAL TEMPORARY') ORDER BY table_name;
>
> So I presume the result set will be in a text format, right? For all
> columns?

Yes. Aside from fetching from a binary cursor, I don't think there
is any other way to get binary results from a PQexec() call.
In particular, bytea columns come back encoded as text
according to the bytea_output setting.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite