How can I easily and effectively support (language) translation of column values? - Mailing list pgsql-general

Hi,

I have some problems supporting "translatable columns" in a way that
is both efficient and comfortable for the programmer who has to write
SQL-Queries (we use iBatis)

Suppose I have a simple table that contains an id field and (amongst
others) a name field. Let's call this table foo. The names are english
names.

These english names need to be translatable (and the translations have
to be "by id", not "by name"; for example: the pair (1, 'foobar') has
to be translated as (1, 'barfoo'), but (2, 'foobar') might be (2,
'baz') and not (2, 'foobar')

I've tried to solve the problem in the following way (if you know of a
better method, please tell me)

I created a table foo_translations, that contains the columns foo_id,
lang_id, and (translated) name:

  SELECT * FROM foo WHERE id = 1;
   id |  name
  ----+--------
    1 | foobar

  SELECT * FROM foo_translations WHERE foo_id=1;
  foo_id | lang_id |  name
  --------+---------+--------
        1 |       1 | barfoo
        1 |       2 | boofoo

Now, whenever I create a query (I use iBatis and therefore more or
less write each query by hand), I can get the result I want by writing
something like this:

  SELECT foo.id, COALESCE(foo_translations.name, foo.name) AS name
FROM foo LEFT JOIN foo_translations ON foo.id=foo_id AND lang_id=?
WHERE id = ?

While this works, it's quite cumbersome having to do this for every
query that involves tables with "translatable names".

So my first idea was to create a view that will give me the correctly
translated fields, like

  CREATE VIEW foo1 AS SELECT foo.id, COALESCE(foo_translations.name,
foo.name) AS name FROM foo LEFT JOIN foo_translations ON foo.id =
foo_translations.foo_id AND foo_translations.lang_id = 1;

This way I could rewrite the last SELECT-statement (assuming the
language-code is 1) as simply

  SELECT * FROM foo1 WHERE id = ?

But this would mean I'd have to define (potentially) 50+ VIEWS for
every table with translatable fields and I'd have the create a new
SQL-Query everytime I use it (because I'd have to insert the language
code right after "FROM foo", and I don't think PreparedStatement (we
use Java) can handle this kind of placeholder "FROM foo?" )

So what I really need is a view that takes a parameter. Unfortunately,
it seems like these don't exist. I can work around it by using a
stored proc:

  CREATE FUNCTION foo(int) RETURNS SETOF foo AS $$ SELECT foo.id,
    COALESCE(foo_translations.name, foo.name) AS name FROM foo LEFT JOIN
    foo_translations ON foo.id=foo_id AND lang_id=$1$$ LANGUAGE SQL;

While this works - I can now define a query like

  SELECT * FROM foo(?) WHERE id=?

- the planner has no clue how to optimize this (dummy table contains 1
million entries):

  EXPLAIN ANALYSE SELECT * FROM foo(1) WHERE id=1;
                                                  QUERY PLAN
  ----------------------------------------------------------------------------------------------------------
   Function Scan on foo  (cost=0.00..15.00 rows=5 width=36) (actual
time=2588.982..3088.498 rows=1 loops=1)
     Filter: (id = 1)
   Total runtime: 3100.398 ms
  (3 rows)

which obviosly is intolerable. So, do I have to bite the bullet and
use the ugly "COALESCE/JOIN" statements everywhere or is there a
better way?

Any help is appreciated. Thanks in advance,
  Dennis