By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

Multilingual db

P: n/a
Hi,

I'm new to SQL/PostgreSQL programming so forgive me if my questions are
to dumb?

I'm designing a multilingual db and I don't know if I'm on the right track.
Please advise.

The basic structure of the tables:

Common Info
| id | ...

localize info
| id | lang | country | ...
Now here is what I came up with.

1. let the client do all the work of getting the right localize info:

SELECT * FROM table_name
WHERE id=1 AND ((lang='en' AND (country='US' OR country='')) OR
(lang='' AND country=''))
ORDER BY lang DESC, country DESC LIMIT 1;
2. Add function for retrieving the localized info:
get_local_info(id, table_name, lang, country);

Retrieve by:
SELECT * FROM get_local_info(1, 'table_name', 'en', 'US') AS (id
integer, lang char(2), country char(2), ...);
Q1. Is it possible somehow to use table definition as column definition
list in AS clause?

Q2. I'd like to simplify the query from section 1 to
SELECT * FROM table_name WHERE id=1 AND lang='en' AND
country='US' ;
For that purpose I need to trap the select query and handle it
by myself. Is it possible?

Q3. Is there a better way to approach the subject?

Thanks.
Yaniv.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.