Re: internationalizing text - Mailing list pgsql-general
From | Michael Fork |
---|---|
Subject | Re: internationalizing text |
Date | |
Msg-id | Pine.BSI.4.21.0103231353580.20588-100000@glass.toledolink.com Whole thread Raw |
In response to | Re: internationalizing text (will trillich <will@serensoft.com>) |
List | pgsql-general |
You can use a where clause when selecting from a veiew, so the following should work for you: CREATE VIEW my_view AS SELECT o.*, n.descr, n.lang FROM nation n, org o WHERE o.nation = n.code SELECT * FROM my_view WHERE n.lang = someKnownQuantity Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Fri, 23 Mar 2001, will trillich wrote: > 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! >
pgsql-general by date: