Search Postgresql Archives

Re: Transparent i18n?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux