Hi Laura,
Did you consider using hstore to store language and data as a kvp? For example:
b2bc_owner@b2bcreditonline=# create table langtest(pageid text, objectid text, objectdata hstore, constraint langtest_pk primary key (pageid, objectid));
CREATE TABLE
CREATE TABLE
b2bc_owner@b2bcreditonline=# insert into langtest values ('zz', 'abc', '"en"=>"en for abc","de"=>"de for abc"');
INSERT 0 1
b2bc_owner@b2bcreditonline=# insert into langtest values ('zz', 'def', '"en"=>"en for def"');
INSERT 0 1
INSERT 0 1
b2bc_owner@b2bcreditonline=# insert into langtest values ('zz', 'def', '"en"=>"en for def"');
INSERT 0 1
b2bc_owner@b2bcreditonline=# create or replace function langtestfunc(text, text, text[]) returns text language sql as $$ select a.data from langtest as t, unnest(t.objectdata->$3) as a(data) where t.pageid = $1 and t.objectid = $2 and a.data is not null limit 1 $$;
b2bc_owner@b2bcreditonline=# select langtestfunc('zz', 'abc', array['de', 'en']);
langtestfunc
--------------
de for abc
(1 row)
langtestfunc
--------------
de for abc
(1 row)
b2bc_owner@b2bcreditonline=# select langtestfunc('zz', 'def', array['de', 'en']);
langtestfunc
--------------
en for def
(1 row)
langtestfunc
--------------
en for def
(1 row)
Just a thought.
Cheers,
Steve
On Wed, Jun 2, 2021 at 6:09 AM Laura Smith <n5d9xq3ti233xiyif2vp@xxxxxxxxxxxxx> wrote:
Hi,
I'm creating a Postgres backend for an internal tool which is essentially a very simple implementation of multi-lingual CMS.
So far my thoughts are along the lines of the below, but I would appreciate a second (or more !) pair of eyes from some Postgresql gurus. I am especially interested in feedback and suggestions in relation to the following questions:
(a) Is this going to work as expected (i.e. have I missed some obvious foot-guns ?)
(b) Is this manner of doing things reasonably efficient or are there better ways I should be thinking of ? (bear in mind the schema is not set in stone, so completely out of the box suggestions welcome !).
The basic design concept (oversimplified) is: For each page, you have one or more objects and those objects may have content in one or more languages.
create table langtest(
pageid text not null,
objectid text not null ,
objectlang text not null,
objectdata text not null);
create unique index on (pageid,objectid,objectlang);
insert into langTest(pageID,objectID,objectLang,objectData) values ('zzz','abc','en','Lorem ipsum dolor sit amet');
insert into langTest(pageID,objectID,objectLang,objectData) values ('zzz','abc','de','Amet sit dolor ipsum lorem');
insert into langTest(pageID,objectID,objectLang,objectData) values ('zzz','def','en','Dolor ipsum amet sit lorem');
select distinct on(objectid)objectid,objectlang,pageid,objectdata from langTest where pageid='zzz' and objectLang = any('{de,en}'::text[]) order by objectid,array_position('{de,en}'::text[],objectLang);
(The idea being that the select query will be wrapped into a function which the frontend will call, passing a list of elegible languages as input)
Thanks !
Laura
