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
