Re: Transparent i18n?

Hi Steve.  I have been a bit puzzling over a similar issue - not i18
for interface but for text data and trying to sort out a solution so I
will be interested to hear additional advice as well.  When I wrote to
the list a couple of weeks back (look for my posting around the 17th) I
was looking at doing something with a normalized structure but now I
don't think this is going to work that well.  It was suggested that I
look at an array.  I am looking at a multidimensional array to do this.
  I am just reading up on postgres support for arrays.

I think my table will be pretty simple;
CREATE TABLE multi_language (
    id                                              SERIAL,
    lang_code_and_text            TEXT[][]
);

So records would look like:

1, {{'en','the brown cow'},{'fr','la vache brun'}}
2, {{'en','the blue turkey'},{'fr','la dandon bleu'}}

I have another table with language codes ie en, fr, etc.  When
languages are added, I would just append to array for whole table.  The
trouble for me is more of getting the data out in postgres because
retrieving the raw array will be incompatible syntax for python and I
would have to manipulate results. Quite frankly I want for this to be
done in Postgres so I only have to retrieve query results.  If I cant
it would be a pain unless I can think of something else because the
issue is going to be the keys and values in my languages table working
with the array.

For example, if I have a serial table containing my languages and add 2
entries english and french, I would then have two elements in my array
and it wouldn't be so bad because I could use the id as a key to get
the value back out through a query.  But say I delete french (and
remove second element in entries for my table) and add spanish, now I
have an language id of 3 and two elements in my array that can't call
each other properly.  In python, arrays are called dictionaries and you
can easily grab the an element doing something like
lang_code_and_text['en'] to get the value of the en (english) key.

I was hoping you could call the multi-language text out of the array
with a text key instead of a numeric index
but it appears Postgres will only let you do it this way or get results
from slices as far as I can tell.  Maybe someone else on the list has
some advice to offer here.

ie.

SELECT language_text[1][1] AS language_code,
  language[1][2] AS text
FROM language_text;


Regards,
David