Coalesce translations according to language priority

language_coalesce.sql

drop extension if exists tablefunc;
drop table if exists texts;
drop table if exists langs;


-- load tablefunc extension for crosstab
create extension tablefunc;
-- crosstab only allows single column - define int and varchar tuples for this purpose
DROP TYPE IF EXISTS intT;
CREATE TYPE intT AS  (module int, id int );
DROP TYPE IF EXISTS strT;
CREATE TYPE strT AS (lang varchar, txt varchar);


create table texts
(   module int not null
,   id int not null
,   lang varchar not null
,   txt varchar not null);
create table langs
(   first varchar not null
,   lang varchar not null
,   priority int not null);

insert into texts (module, id, lang, txt) values
    (0,0,'def','HelloDEF'),
    (0,1,'def','WorldDEF'),
    (0,0,'en','Hello'),
    (0,1,'en','World'),
    (0,0,'de','Hallo'),
    (0,1,'de','Welt'),
    (0,0,'jp','Konnichiwa'),
    (0,1,'fr','Monde'),
    (1,0,'def','Switzerland'),
    (1,0,'de','Schweiz'),
    (1,0,'fr','Suisse'),
    (1,0,'jp','Suisu');

insert into langs (first, lang, priority) values
    ('jp','jp',0),
    ('jp','en',1),
    ('jp','def',2),
    ('en','en',0),
    ('en','def',1),
    ('en','def',2),
    ('de','de',0),
    ('de','en',1),
    ('de','def',2),
    ('fr','fr',0),
    ('fr','de',1),
    ('fr','def',2);

select (mod_id).*, (coalesce(a,b,c)).* -- unpack tuple types here to get nice table
from crosstab($$
    select (module,id) as mod_id, priority, (lang,txt) as lang_txt -- order is important here
    from texts
    join langs using (lang)
    where first = 'fr' --! language goes here
    and module = 0 --! module integer goes here
    order by id, priority asc
$$,$$
    select generate_series(0,2) -- as number of outputs has to be fixed (# = 3), always return 0,1,2 here.
$$) as ct (mod_id intT, a strT, b strT, c strT);

-- Problems
-- 1. Each language has to have two fallbacks (i.e. language itself with prio 0, then FB1 and FB2)
--      This is because the format of the crosstab table has to be specified in sql (a varchar, b varchar, c varchar) - this is not dynamic (even though type should be possible to infer)
--      The coalesce query that gets the first of a,b,c also does not support wildcards
-- 2. Prepared statements do not work. Language and module need to be added manually at two locations.
--      The reason for this is that PGSQL does not support parameters in inner queries ($$ <inner query>> $$). PSQL macros (\set x, :x) also don't work
--      Example:
--      The following query does 'compile'
--
        -- DEALLOCATE get_module;
        -- PREPARE get_module (int, varchar) AS
        --     select module,id,lang,txt
        --     from
        --     (   select id, coalesce(a,b,c) as lang
        --         from crosstab($$
        --             with p as (select * from langs where first = $2) -- language goes here
        --             select id, priority, lang
        --             from texts
        --             join p using (lang)
        --             where module = $1 -- module integer goes here
        --             order by id, priority asc
        --         $$,$$
        --             select distinct priority from langs where first = $2 -- language goes here
        --         $$) as ct (id int, a varchar, b varchar, c varchar)) as lang_match
        --     join texts using (id,lang)
        --     where module = $1; -- module integer goes here
--
--      But execution yields an error
--
        -- EXECUTE get_module(0, 'jp');