PostgreSQL getting translatable data?

Question

PostgreSQL

The database stores information about posts and their translations.
The structure is as follows:
Posts: id, category_id, date_updated, date_created
PostTranslations: post_id, language_code, title, text
Languages: code, name
Task: get translated posts in the specified language; if there is no language, get the translation in the default language.
I originally created a 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'

There was a problem: at VIEW it is impossible to set parameters, and it is necessary for me to receive translation in the language specified by the user from application. So I decided to make a stored procedure.

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;

I got a couple of questions:

• If the user specifies the language en, how to get rid of the extra JOIN?
  
• Does a stored procedure affect performance? If I have, for example,
  1000000 posts, and I want to display them page by page, does the database process a query like: in the same way as if it worked with a table? Or is all the data unloaded (as written in the procedure), and then select is performed on the received data?
  SELECT * FROM posts_list('ru') LIMIT 30 OFFSET 90
  

UPD:
Empirically revealed that the procedure first unloads all the data, and then filtering is carried out on them. This is unacceptable. Help me figure out how to get translatable data from the database (the default language is English, and the desired language is set by the user).

Answer 1

I answered you in the question about mapping.
You are making a performance, it must also have a language in it.
Already to the code of your solution, you do filtering by language and page selection.
The variant with the procedure is also normal, but it is complicated. First, you need to add Limit and offset to the procedure to fetch paginated data. Secondly, it is very inconvenient if you need to filter this selection, you will have to fence additional filter structures in order to pass them to filters later.
What is your AP? If C#, then it's easier to use the approach when you do something like this:

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;
  }
}

and then,
In general, such an approach is possible to some extent in most PLs.