Re: TODO item:Allow to_date() and to_timestamp() accept localized month names - Mailing list pgsql-hackers

> 
> Surely it should be the inverse of the solution for output, 
> eg TMMon selects localized input.
> 

After some investigation in how gettext works, I would like to have your
opinion about how to 
implement this TODO item.

Starting with TO_CHAR: 

When the TM prefix is used in TO_CHAR (for example TMMonth),
the routine, internally calls functions like the localize_month and
localize_day to get the localized value.
These functions rely on the current locale category that is internally
loaded by GetText "engine". 
The GetText engine does not load any other locate category unless SET
LC_MESSAGES or alike is given.


Now back to TO_DATE: 

For this there are three solution that I can think of.

1. For TO_DATE to return localized data we can implement the TM prefix logic
which is already
implemented in TO_CHAR. Copying and modifying it for TO_DATE should be
feasible.
The downside of this solution is that TO_DATE will only return localized
values base on current locale. (no third parameter)

2. For TO_DATE to behave like Mr. Oracle's version of to_date (
to_date('01-OCT-99''DD-MON-YY,'nls_date_language = nl_NL'); ) We might (must
be tested to see if it is even possible) be able to have a smaller copy of
gettext engine that only contains day and month names and have TO_DATE use
it to return values based on the given locale. This solution is much harder
to implement of course.

3. Have TO_DATE to switch the locale back and forth by internally executing
SET LC_* to the given locale.
Please note that loading another locale category forced by SET LC_* is
costly. (look at GetText source). 

Any thoughts?

Regards,
Gevik Babakhani
------------------------------------------------
PostgreSQL NL       http://www.postgresql.nl
TrueSoftware BV     http://www.truesoftware.nl
------------------------------------------------




> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org 
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of 
> Peter Eisentraut
> Sent: Tuesday, February 05, 2008 9:04 AM
> To: pgsql-hackers@postgresql.org
> Cc: Alvaro Herrera; Tom Lane; Gevik Babakhani
> Subject: Re: [HACKERS] TODO item:Allow to_date() and 
> to_timestamp() accept localized month names
> 
> Alvaro Herrera wrote:
> > Nevertheless, I think there's something interesting missing here, 
> > which is a sort of strftime's %c format string.
> 
> I think the Oracle way to do that would be to_char() with one 
> argument and setting NLS_DATE_FORMAT.
> 
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 6: explain analyze is your friend