How to order by a varchar choosing the collate order with a case-when switch, in PostgreSQL

Question

This simple function returns an ordered list of title strings.

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    |

However, when I try to introduce a collation order with collate I am not being able to get the syntax right to set the proper collation order based on the parameter _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;
$$;

The error is SQL Error [42601]: ERROR: syntax error at or near "case".

I have unsuccessfully tried positioning the case everywhere in the order by clause. Maybe the "en_US" is not considered a scalar value?

---

EDIT I've added where lang_code = _lang_code after Laurenz Albe comment. This was a missing clause when traslating from my real problem to this simplified example.

However the issue with the case remains with the same SQL error.

---

SOLUTION

As @Lorenz Albe pointed in a comment, "en_US" is an identifier, not a scalar value. This prevents the case-when structure from returning it in any of its when branches. So no SQL way happens to exist.

As a work around, dynamic SQL from @doctore or moving the case to embrace the whole sentence, are both inelegant but functional solutions to the problem.

Answer 1

Taking into account you are using the parameter _lang_code to choose the "internal language" to filter. The following PL/SQL code allow you change the collate in the final query dynamically:

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

Now you can execute your tests or even uncomment the raise exception to be sure about the suitable "final query":

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

PD: I have changed the type of _lang_code and lang_code to varchar because I assume tlang_code is a custom one.

Answer 2

Write your case for order by like below:

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

Answer 3

@doctore solution forces a PL/PGSQL function, so does this another approach of moving the case-when to embrace the whole select sentence. They both are far from elegant, but prove the question had a sense.

Unfortunately, I haven't found the cause of the syntax error in my original function.

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