Multilingual db

[kpub]

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)