PostgreSQL получение переводимых данных?

Question

[Виталий Катомин]

В базе данных хранится информация о постах и их переводах.
Структура следующая:
Posts: id, category_id, date_updated, date_created
PostTranslations: post_id, language_code, title, text
Languages: code, name

Задача: получить переведенные посты на указанном языке; в случае отсутствия языка получить перевод на языке по умолчанию.

Изначально я создал VIEW:

CREATE OR REPLACE VIEW posts_view(id, title, text)
  AS
    SELECT
      p.id AS id,
      coalesce(pt.title, ptd.title) AS title,
      coalesce(pt.text, ptd.text) AS text
    FROM posts l
      LEFT JOIN post_translations pt ON p.id = pt.post_id AND pt.language_code = 'ru'
      LEFT JOIN post_translations ptd ON p.id = ptd.post_id AND ptd.language_code = 'en'

Возникла проблема: у VIEW нельзя задавать параметры, а мне необходимо из приложения получать перевод на указанном пользователем языке. Поэтому я решил сделать хранимую процедуру.

CREATE OR REPLACE FUNCTION posts_list(IN translation_code CHAR(2))
  RETURNS TABLE(id INT, title TEXT, text TEXT)
AS $$
    SELECT
      p.id AS id,
      coalesce(pt.title, ptd.title) AS title,
      coalesce(pt.text, ptd.text) AS text
    FROM posts l
      LEFT JOIN post_translations pt ON p.id = pt.post_id AND pt.language_code = translation_code
      LEFT JOIN post_translations ptd ON p.id = ptd.post_id AND ptd.language_code = 'en';
$$
LANGUAGE SQL;

У меня возникла пара вопросов:

• Если пользователь укажет язык en, как избавиться от лишнего JOIN?
  
• Влияет ли хранимая процедура на производительность? Если у меня, например,
  1000000 постов, а я хочу выводить их постранично, обрабатывает ли БД запрос вида:
  SELECT * FROM posts_list('ru') LIMIT 30 OFFSET 90 также, как если бы она работала с таблицей? Или выгружаются все данные (как написано в процедуре), а потом выполняется select над полученными данными?
  

UPD:
Опытным путем выявил, что процедура сначала выгружает все данные, а потом по ним осуществляется фильтрация. Это неприемлемо. Помогите придумать, как получать из БД переводимые данные (язык по умолчанию английский, а желаемый язык задается пользователем).

Answer 1

[Александр Кузнецов]

Я же Вам ответил в вопросе про mapping.
Вы делаете представление, в нем так же должен быть еще и язык.
Уже к коде своего решения делаете фильтрацию по языку и выборку страницы.
Вариант с процедурой тоже нормальный, но он сложный. Во-первых, Вам нужно в процедуру добавить Limit и offset, для выборки постраничных данных. Во-вторых, он очень не удобен, если вам потребуется фильтрация данной выборки, Вам придется городить дополнительные структуры фильтров, чтобы передавать их потом в фильтры.

У Вас какой ЯП? Если C#, то проще все же использовать подход, когда вы делаете что-то такое:

public static class ViewPostExt
{
	public static IQueryable<ViewPost> GetViewPosts(this DbContext db)
	{
		var currLang = GetCurrentNeutralCulture();
		return db.Set<ViewPost>().AsNoTracking().Where(x=>x.LangCode == currLang);
	}
	
	private static string GetCurrentNeutralCulture()
	{
		return Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName;
	}
}

и затем,

var posts = (from p in db.GetViewPosts()).Skip(PagesToSkip).Take(Limit).ProjectTo<PostDto>().ToList();

В целом, подобный подход возможен в той или иной степени на большинстве ЯП.