945gnYMD

1drop extension if exists tablefunc;
2drop table if exists texts;
3drop table if exists langs;
4
5
6-- load tablefunc extension for crosstab
7create extension tablefunc;
8-- crosstab only allows single column - define int and varchar tuples for this purpose
9DROP TYPE IF EXISTS intT;
10CREATE TYPE intT AS  (module int, id int );
11DROP TYPE IF EXISTS strT;
12CREATE TYPE strT AS (lang varchar, txt varchar);
13
14
15create table texts
16(   module int not null
17,   id int not null
18,   lang varchar not null
19,   txt varchar not null);
20create table langs
21(   first varchar not null
22,   lang varchar not null
23,   priority int not null);
24
25insert into texts (module, id, lang, txt) values
26    (0,0,'def','HelloDEF'),
27    (0,1,'def','WorldDEF'),
28    (0,0,'en','Hello'),
29    (0,1,'en','World'),
30    (0,0,'de','Hallo'),
31    (0,1,'de','Welt'),
32    (0,0,'jp','Konnichiwa'),
33    (0,1,'fr','Monde'),
34    (1,0,'def','Switzerland'),
35    (1,0,'de','Schweiz'),
36    (1,0,'fr','Suisse'),
37    (1,0,'jp','Suisu');
38
39insert into langs (first, lang, priority) values
40    ('jp','jp',0),
41    ('jp','en',1),
42    ('jp','def',2),
43    ('en','en',0),
44    ('en','def',1),
45    ('en','def',2),
46    ('de','de',0),
47    ('de','en',1),
48    ('de','def',2),
49    ('fr','fr',0),
50    ('fr','de',1),
51    ('fr','def',2);
52
53select (mod_id).*, (coalesce(a,b,c)).* -- unpack tuple types here to get nice table
54from crosstab($$
55    select (module,id) as mod_id, priority, (lang,txt) as lang_txt -- order is important here
56    from texts
57    join langs using (lang)
58    where first = 'fr' --! language goes here
59    and module = 0 --! module integer goes here
60    order by id, priority asc
61$$,$$
62    select generate_series(0,2) -- as number of outputs has to be fixed (# = 3), always return 0,1,2 here.
63$$) as ct (mod_id intT, a strT, b strT, c strT);
64
65-- Problems
66-- 1. Each language has to have two fallbacks (i.e. language itself with prio 0, then FB1 and FB2)
67--      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)
68--      The coalesce query that gets the first of a,b,c also does not support wildcards
69-- 2. Prepared statements do not work. Language and module need to be added manually at two locations.
70--      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
71--      Example:
72--      The following query does 'compile'
73--
74        -- DEALLOCATE get_module;
75        -- PREPARE get_module (int, varchar) AS
76        --     select module,id,lang,txt
77        --     from
78        --     (   select id, coalesce(a,b,c) as lang
79        --         from crosstab($$
80        --             with p as (select * from langs where first = $2) -- language goes here
81        --             select id, priority, lang
82        --             from texts
83        --             join p using (lang)
84        --             where module = $1 -- module integer goes here
85        --             order by id, priority asc
86        --         $$,$$
87        --             select distinct priority from langs where first = $2 -- language goes here
88        --         $$) as ct (id int, a varchar, b varchar, c varchar)) as lang_match
89        --     join texts using (id,lang)
90        --     where module = $1; -- module integer goes here
91--
92--      But execution yields an error
93--
94        -- EXECUTE get_module(0, 'jp');