Hi there,
sorry if just misunderstanding but we have contrib/hstore available from
http://www.sai.msu.su/~megera/postgres/gist/
which could be used for storing as many languages as you need.
It's sort of perl hash.
Oleg
On Mon, 4 Jul 2005, David Pratt wrote:
Hi Greg. Not sure about this one since I have never made my own type. Do you
mean like an ip to country type of situation to guess locale? If so, I am
using a ip to country table to lookup ip from request and get the country so
language can be passed automatically to display proper language (but I need
some translation work done first before I can activate this). I will also
use this for black listing purposes and other things so multi purpose.
I have got a good part of what I wanted working so far. I am just working on
language update delete trigger since there does not appear to be a direct way
of surgically removing a specific element from an array in postgres unless I
have missed something. For example if I knew spanish was 3rd array in my
multi-dimensional array of say 10 lang/translation arrays in the array
containing all translations - to remove just this one without having rewrite
the array and update the field (which is what I am hoping to complete today).
So my language update delete trigger needs to scan the array for
lang/translation for deletion, update language key for each language from a
reference field (other than for the language being deleted), rewrite the
array without the lang/translation that was deleted, and then update the
field with rewritten array. Sounds worse that it really is since the
multidimensional array containing each lang/translation array is same length
and you are performing this by iterating with a loop through records in
multi_language table. Further, each translation can be compared by key (for
me this is the iso language code). Also, realistically how many times do you
need to add and drop languages. And number of languages in use for me will
likely never exceed say 20. So this process, even with large numbers of
multi-language fields should not be that problematic even if you had say a
few thousand text fields fields you wanted translations available for. I
think you would still be looking at milliseconds to perform this. This will
be an after type trigger (after deletion). I guess I will see what
performance is like when I am finished - so far it is pretty fast for adding.
You also have a sensible structure for multi_language fields where each one
is referenced to multi_language table by id (normalized) with referential
integrity (something I was seeking). The only thing not normalized are
translations which is okay to me since array structure is dynamic yet keys
give you exactly what you want. I am also going to look at Karsten's
material shortly to see how his system works but I am interested in following
through with what I started first with arrays approach since I am happy with
what I am seeing.
Regards,
David
On Monday, July 4, 2005, at 12:06 PM, Greg Stark wrote:
I wonder if you could make an SQL type that used text[] as its storage
format
but had an output function that displayed the correct text for the "current
locale". Where "current locale" could be something you set by calling a
function at the beginning of the transaction.
Do pg_dump and all the important things use the send/receive functions not
the
input/output functions? so even though this output function loses
information
it wouldn't cause serious problems?
You would still need a way to retrieve all the languages for the cases like
administrative interfaces for updating the information. I'm not entirely
convinced this would be any better than the alternative of retrieving all
of
them by default and having a function to retrieve only the correct
language.
--
greg
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
