Обсуждение: [SQL] Determining the DATE format with libpq

Hi all,

The date format can be defined with SET DateStyle.

When fetching result set values using the libpq C API, we get plain text from PQgetvalue(), representing a date in the
currentformat.
 

We need to extract date parts (year/month/day) to fill our own DATE type structure...

Is there a way to know the current date format used in the SQL session?

Ideally, we expect a libpq API.

We cannot do a "SHOW DateStyle" each time we executing a SELECT!

Other DB client C APIs provide a C structure to hold DATE values so it's easy to get year/month/day parts.

Seb

Sebastien FLAESCH <sf@4js.com> writes:
> Is there a way to know the current date format used in the SQL session?
> Ideally, we expect a libpq API.

Use PQparameterStatus().

> We cannot do a "SHOW DateStyle" each time we executing a SELECT!

You don't have to --- the server automatically reports changes in the
value of that parameter.
        regards, tom lane

Whoooooo that's perfect!!!

I hope it has not a big cost?

Can I use this each time I fetch a result set?

Even row by row with a server cursor?

Seb

On 08/24/2017 05:26 PM, Tom Lane wrote:
> Sebastien FLAESCH <sf@4js.com> writes:
>> Is there a way to know the current date format used in the SQL session?
>> Ideally, we expect a libpq API.
>
> Use PQparameterStatus().
>
>> We cannot do a "SHOW DateStyle" each time we executing a SELECT!
>
> You don't have to --- the server automatically reports changes in the
> value of that parameter.
>
>             regards, tom lane
>
>

I appologize for my poor English...
I was just wondering about the overhead of a call to PQparameterStatus().
After some tests, it appears to be minor.
Anyway, thanks for your help!
Seb

On 08/24/2017 05:44 PM, Sebastien FLAESCH wrote:
> Whoooooo that's perfect!!!
>
> I hope it has not a big cost?
>
> Can I use this each time I fetch a result set?
>
> Even row by row with a server cursor?
>
> Seb
>
> On 08/24/2017 05:26 PM, Tom Lane wrote:
>> Sebastien FLAESCH <sf@4js.com> writes:
>>> Is there a way to know the current date format used in the SQL session?
>>> Ideally, we expect a libpq API.
>>
>> Use PQparameterStatus().
>>
>>> We cannot do a "SHOW DateStyle" each time we executing a SELECT!
>>
>> You don't have to --- the server automatically reports changes in the
>> value of that parameter.
>>
>>             regards, tom lane
>>
>>
>
>
>

Seems that even if one can change the date style while fetching rows from a
server cursor, the date format returned by PQgetvalue() does not change in
the middle of a result set processing, which is obviously better.

So we just need to inspect the date format when opening the cursor and
stuck to that format for result set values processing.

Seb