Re: internationalizing text

On Fri, Mar 23, 2001 at 09:29:17AM -0000, Michael Ansley wrote:
> I think that the standard way to do this is to use a resource identifier,
> and then have a separate table with all strings.  That's the way that most
> internationalisation is done in programs, and it's probably not bad for
> databases either.
>
> So maybe:
>
>      create table something (
>         id serial,
>         yadayada int4,
>         whatever date,
>         mumble float8,
>         ...
>                 id_resource int4 references something_text(id)
>     );
>     create table something_text (
>         id int4,
>         lang varchar(5), -- language code 'en-us','it','jp'...
>         descr varchar(50)
>     );

i was thinking that it was probably the normalization-friendly
version that would be most flexible.

but here's where i run into snags:

    create table nation (
        code char(2),
        lang varchar(5),
        descr varchar(50),
    primary key (code)
    );
    insert into nation values ('us','en','United States');
    insert into nation values ('us','fr','Etats Unis');

    create table org (
        ...
        postcode varchar(12),
        nation char(2) references nation(code),
        ...
    );

how do i get the proper "descr" field from table "nation" that's
in language "lang=xyz" using a view, when i don't know which
language will be used at 'compile time'? a straight select/view
will show ALL languages for EACH 'record' sought:

    select o.*,n.descr
    from   nation n, org o
    where  o.nation = n.code
    -- and n.lang = someUnknownQuantityToBeDeterminedLater
    -- /* simple global variables would solve this, ahem... :) */
    ;

right now i've got all the data-driven stuff built into the
sql/plpgsql; the perl code does only the presenting of the
data... i'd like to keep the functions separated, but it doesn't
look possible here.  ideas?

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
        -- Isaac Asimov, 'The Genetic Code'

will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!