Skip site navigation (1) Skip section navigation (2)

Re: Use arrays to store multilanguage texts

From: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Use arrays to store multilanguage texts
Date: 2004-05-30 18:01:39
Message-ID: 20040530200139.B649@hermes.hilbert.loc (view raw or flat)
Thread:
Lists: pgsql-general
http://savannah.gnu.org/cgi-bin/viewcvs/*checkout*/gnumed/gnumed/gnumed/server/sql/gmI18N.sql?rev=1.15

Works for me. You'll have to make sure you get the entire
string for the URL. It may have been wrapped by your mail
handling agent.

> > - i18n_curr_lang holds the (ISO) language string per user
> 
> I'm interested in this part. Is there a facility for having variables on the
> postgres side that you can set once at the beginning of a session and then
> refer to in functions? 
We set them the first time a user connects to a particular
database. Unless they are changed later (possible anytime with
immediate transparent switching of languages in queries and
views) the language will stay the same across sessions/
clients/ connections.

> I instead made my application insert lang_en,lang_fr etc into the queries as
> appropriate. This made the application code a little uglier than necessary,
> but it's not too bad, I put the function name in a variable and call
> $xlate(column) in a double-quoted string.

We then use select _(fruits.name) style queries. The _()
function uses the language in i18n_curr_lang (for
CURRENT_USER).

> I think having a global postgres variable would be a nice shortcut, though I
> fear it would make it harder to do things like index lang_en() and lang_fr().
I'd think on could index i18n_translations just fine:

create unique index on i18n_translations(trans) where lang='xx';

> Do you cheat and make your _() IMMUTABLE? It's only immutable if you guarantee
> that i18n_currr_lang can never change in the middle of a query.
Given that it'd take a writing query that should run in a
transaction it shouldn't change within a read query. However,
I'm not too sure about views, like we use translated columns
in views like this:

create view a_view as
select
    bla as bla,
    _(bla) as l10n_bla
...

> But even then
> you're lying to the server and indexing _() would do strange things, for
> example. In fact I think it would produce corrupted indexes if it worked at
> all.
I haven't experienced any of that but it may well happen -
dunno.

> > - v_missing_translations is a view of, well, ...
> 
> This is the one thing that the array solution can't do. But in my case it's
> dynamic text and both languages are being provided at the same time by the
> same people. I'm not going to have a translator going through batches of them
> like gettext.
Well, you wouldn't *have* to use that view, it's just for
convenience. You could just as well insert the dynamic text
into i18n_translations (lang, orig, trans) whenever dynamic
text is inserted. We mainly use that (+ i18n()) for gettext
style batch translation of static application text. Except
that all the lookup is handled by _() on the server right
inside the query :-)

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

In response to

pgsql-general by date

Next:From: Karsten HilbertDate: 2004-05-30 18:04:50
Subject: Re: Error handling in stored functions/procedures
Previous:From: John WellsDate: 2004-05-30 17:55:01
Subject: Large table search question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group