[HACKERS] Setting locale per connection
Hi all,
I'm new to the list, so don't flame at the first date ;).
I usually use PostgreSQL for multiple languages, so I needed to
set locale per connection, or can change the locale on the fly.
I don't know if there is any such ability integrated in or not,
so I have wrote my 10lines function as a wrapper around
setlocale, that is attached. So what I do is just a simple
"SELECT locale('LC_COLLATE', 'fa_IR');" at connection time. Let
me know if there is any standard way already implemented.
Another silly question, isn't any way to get rid of seqscan, when
doing 'SELECT count(*) FROM tab;'?
Yours,
behdad
--
Behdad Esfahbod 11 Tir 1382, 2003 Jul 2
http://behdad.org/ [Finger for Geek Code]
If you do a job too well, you'll get stuck with it.
#include
#include
bool
pgbe_setlocale (void *cat, void *loc)
{
int category;
void *catstr;
if (!loc)
return false;
if (cat)
catstr = VARDATA (cat);
else
catstr = "LC_ALL"; /* default to LC_ALL */
#define CHECKCATEGORY(s, i) if (!strcmp (s, catstr)) category = i;
/* *INDENT-OFF* */
CHECKCATEGORY ("LC_ALL", LC_ALL) else
CHECKCATEGORY ("LC_COLLATE", LC_COLLATE) else
CHECKCATEGORY ("LC_CTYPE",LC_CTYPE) else
CHECKCATEGORY ("LC_MESSAGES", LC_MESSAGES)else
CHECKCATEGORY ("LC_MONETARY", LC_MONETARY)else
CHECKCATEGORY ("LC_NUMERIC", LC_NUMERIC) else
CHECKCATEGORY ("LC_TIME", LC_TIME)else
return false;
/* *INDENT-ON* */
#undef CHECKCATEGORY
return setlocale (category, VARDATA (loc)) ? true : false;
}
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Setting locale per connection
On Tue, 1 Jul 2003, Stephan Szabo wrote:
>
> On Wed, 2 Jul 2003, Behdad Esfahbod wrote:
>
> > I'm new to the list, so don't flame at the first date ;).
> >
> > I usually use PostgreSQL for multiple languages, so I needed to
> > set locale per connection, or can change the locale on the fly.
> > I don't know if there is any such ability integrated in or not,
> > so I have wrote my 10lines function as a wrapper around
> > setlocale, that is attached. So what I do is just a simple
> > "SELECT locale('LC_COLLATE', 'fa_IR');" at connection time. Let
> > me know if there is any standard way already implemented.
>
> Hmm, I'd think there'd be some potential for danger there. I don't play
> with the locale stuff, but if the collation changes and you've got indexed
> text (varchar, char) fields, wouldn't the index no longer necessarily be
> in the correct order?
I read in the FAQ that indexes for text fields is used just if
default C locale is used during initdb, well, humm, is not the
case on most distros. BTW, such a function is really needed to
make Unicode collation algorithms effective. I may be able to
convince my provider to define the function, but I can't convince
him to start the backend with my desired locale!
--
Behdad Esfahbod 11 Tir 1382, 2003 Jul 2
http://behdad.org/ [Finger for Geek Code]
If you do a job too well, you'll get stuck with it.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Setting locale per connection
On Wed, 2 Jul 2003, Tom Lane wrote: > > On Wed, 2 Jul 2003, Behdad Esfahbod wrote: > >> so I have wrote my 10lines function as a wrapper around > >> setlocale, that is attached. > > Indeed, this is exactly why Postgres goes out of its way to prevent you > from changing the backend's collation setting on-the-fly. The proposed > function is a great way to shoot yourself in the foot :-(. If you doubt > it, check the archives from two or three years ago when we did not have > the interlock to force LC_COLLATE to be frozen at initdb time ... > > regards, tom lane So, assuming I want to go for the right way, I've just seen the column-based LC_COLLATE in the TODO list. Is there any more information about that? behdad -- Behdad Esfahbod 11 Tir 1382, 2003 Jul 2 http://behdad.org/ [Finger for Geek Code] If you do a job too well, you'll get stuck with it. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
