Блог

Эта простая функция возвращает упорядоченный список title строк.

 create or replace function testfunction1 ()
returns table (
  id        bigint,
  lang_code tlang_code,
  title     varchar
)
stable language sql as $$
  select 
    id, lang_code, title
  from 
    testable
  order by
    title collate "es_ES";
$$;

select * from testfunction ();

id|lang_code|title           |
--|---------|----------------|
12|DE       |NOCH FESTZULEGEN|
16|DE       |NOCH FESTZULEGEN|
 8|DE       |NOCH FESTZULEGEN|
14|ES       |POR DETERMINAR  |
 6|ES       |POR DETERMINAR  |
10|ES       |POR DETERMINAR  |
 5|EN       |TO BE DETERMINED|
 9|EN       |TO BE DETERMINED|
13|EN       |TO BE DETERMINED|
11|FR       |À DÉTERMINER    |
15|FR       |À DÉTERMINER    |
 7|FR       |À DÉTERMINER    |
  

Однако, когда я пытаюсь ввести порядок сортировки с помощью collate , я не могу правильно настроить синтаксис, чтобы установить правильный порядок сортировки на основе параметра _lang_code .

 create or replace function testfunction2 (_lang_code tlang_code)
returns table (
    id        bigint,
    lang_code tlang_code,
    title     varchar
)
stable language sql as $$
    select 
        id, lang_code, title
    from 
        testable
    where 
        lang_code = _lang_code
    order by
        title collate 
            case _lang_code
                when 'EN' then "en_US" 
                when 'ES' then "es_ES"
                when 'FR' then "fr_FR"
                when 'DE' then "de_DE"
            end asc;
$$;
  

Ошибка SQL Error [42601]: ERROR: syntax error at or near "case" .

Я безуспешно пытался разместить case везде в order by предложении. Может быть, «en_US» не считается скалярным значением?

РЕДАКТИРОВАТЬ, которое я добавил where lang_code = _lang_code после комментария Лоренца Альбе. Это было отсутствующее предложение при переходе от моей реальной проблемы к этому упрощенному примеру.

Однако проблема с case остается с той же ошибкой SQL.

РЕШЕНИЕ

Как указал @Lorenz Albe в комментарии, "en_US" является идентификатором, а не скалярным значением. Это не позволяет case-when структуре возвращать его в любой из своих when ветвей. Таким образом, способа SQL не существует.

В качестве обходного пути, динамический SQL от @doctore или перемещение регистра, чтобы охватить все предложение, являются неэлегантными, но функциональными решениями проблемы.

Принимая во внимание, что вы используете параметр _lang_code для выбора «внутреннего языка» для фильтрации. Следующий код PL / SQL позволяет динамически изменять collate в конечном запросе:

 create or replace function testfunction2 (_lang_code varchar)
returns table (
                  id        bigint,
                  lang_code varchar,
                  title     varchar
              )
language plpgsql
as $$
declare
  final_collate varchar;
  final_query varchar;
begin
  if (_lang_code = 'EN') then
    final_collate := 'en_US';
  elsif (_lang_code = 'ES') then
    final_collate := 'es_ES';
  end if;
  -- Include other use cases you need

  final_query := 'select t.id, t.lang_code, t.title ' ||
                 'from test_table t ' ||
                 'where t.lang_code = ''' || _lang_code || ''' ' ||
                 'order by t.title collate "' || final_collate || '" asc';

  --raise exception 'Final query: %', final_query;

  return query
    execute final_query;
end;$$
  

Теперь вы можете выполнить свои тесты или даже раскомментировать raise exception , чтобы быть уверенным в подходящем «окончательном запросе»:

 select testfunction2('EN')
select testfunction2('ES')
  

PD: Я изменил тип _lang_code и lang_code на varchar , потому что я предполагаю, tlang_code что это пользовательский.

Напишите свой случай для заказа, как показано ниже:

 create or replace function testfunction2 (_lang_code tlang_code)
returns table (
    id        bigint,
    lang_code tlang_code,
    title     varchar
)
stable language sql as $$
    select 
        id, lang_code, title
    from 
        testtable
    order by  
            case _lang_code
                when 'EN' then title collate "en_US" 
                when 'ES' then title collate "es_ES"
                when 'FR' then title collate "fr_FR"
                when 'DE' then title collate "de_DE"
            end asc;
$$;
  

Решение @doctore запускает функцию PL / PGSQL, поэтому это другой подход к перемещению случая, когда для охвата всего предложения select. Они оба далеки от элегантности, но доказывают, что вопрос имел смысл.

К сожалению, я не нашел причину синтаксической ошибки в моей исходной функции.

 create or replace function testfunction3 (_lang_code char(2))
returns table (
  id        bigint,
  lang_code char(2),
  title     varchar
)
stable language plpgsql as $$
begin
  case _lang_code
    when 'EN' then
      return query
      select *
      from testtable t 
      where t.lang_code = _lang_code  
      order by t.title collate "en_US";
     
    when 'ES' then
      return query
      select *
      from testtable t 
      where t.lang_code = _lang_code  
      order by t.title collate "es_ES";

    when 'FR' then
      return query
      select *
      from testtable t 
      where t.lang_code = _lang_code  
      order by t.title collate "fr_FR";

    when 'DE' then
      return query
      select *
      from testtable t 
      where t.lang_code = _lang_code  
      order by t.title collate "de_DE";
     
   end case;
end
$$;