Faz sentido ter CASE .. END em um ORDER BY?

Três pontos que eu levantaria,

1. Esta é uma consulta muito básica, mesmo em sua versão aplicada. Crie um VIEWpara isso. Faça com que seus usuários personalizem as WHEREcondições usando o arquivo VIEW. As funções são caixas pretas para o planejador de consulta. É horrível usá-los dentro de outras funções, só que SQLé embutido. E as funções dinâmicas não obtêm planos em cache.
2. Se você quiser continuar usando plpgsql, use RETURNS QUERY(ou RETURNS QUERY EXECUTE) not RETURNS SETOF. Não há razão para usar RETURNS SETOFcom uma classificação. Tem que ser armazenado em buffer de qualquer maneira, afaik. Você encontrará problemas com qualquer um deles se o conjunto de resultados for maior que work_mem.
3. Não tenho certeza do que seu aplicativo está escrito. Presumo que web. Eu estive na indústria automotiva por uma década, porém, fiz muitas coisas como o Craigslist e ferramentas de postagem para o Craigslist. "Não classifique coisas para usuários no banco de dados" geralmente é uma boa regra de ouro. Não há razão para isso. Solte essas coisas em JSON e deixe-os lidar com isso no navegador. A menos que você esteja mostrando mais de 1000 linhas, nem vale a pena pensar nisso. Basta considerar o tempo de ida e volta de uma torre de telefonia celular. Você nunca terá esse tempo de volta se perguntando sobre esse problema.

Avançando, eu até consideraria agrupar um serviço como o PostgREST, que lida com pedidos completamente arbitrários,

If you care where nulls are sorted, add nullsfirst or nullslast:

GET /people?order=age.nullsfirst
GET /people?order=age.desc.nullslast

Resposta geral

Primeiro, quero abordar a ambigüidade na premissa:

É comum usar parâmetros na parte WHERE das consultas e ter algumas colunas computadas na parte SELECT, mas não tão comum para parametrizar a cláusula ORDER BY.

As colunas calculadas na SELECTparte quase nunca são relevantes para o plano de consulta ou desempenho. Mas "na WHEREparte" é ambíguo.

É comum parametrizar valores na WHEREcláusula, o que funciona para instruções preparadas. (E o PL/pgSQL trabalha com instruções preparadas internamente.) Um plano de consulta genérico geralmente faz sentido independentemente dos valores fornecidos . Ou seja, a menos que as tabelas tenham uma distribuição de dados muito desigual, mas como o Postgres 9.2 PL/pgSQL replaneja as consultas algumas vezes para testar se o plano genérico parece bom o suficiente:

• Desempenho do procedimento armazenado do PostgreSQL

Mas não é tão comum parametrizar predicados inteiros (incluindo identificadores ) na WHEREcláusula, o que é impossível com instruções preparadas para começar. Você precisa de SQL dinâmico comEXECUTE , ou monta as strings de consulta no cliente.

As expressões dinâmicas ORDER BYestão em algum lugar entre as duas. Você pode fazer isso com uma CASEexpressão, mas isso é muito difícil de otimizar em geral. O Postgres pode usar índices com um simples ORDER BY, mas não com CASEexpressões que escondam a eventual ordem de classificação. O planejador é inteligente, mas não uma IA. Dependendo do restante da consulta ( ORDER BYpode ser relevante para o plano ou não - é relevante no seu exemplo), você pode acabar com um plano de consulta abaixo do ideal o tempo todo .
Além disso, você adiciona o menor custo da CASE(s) expressão(ões). E no seu exemplo particular também para váriasORDER BY colunas inúteis .

Normalmente, SQL dinâmico com EXECUTEé mais rápido ou muito mais rápido para isso.

A capacidade de manutenção não deve ser um problema se você mantiver um formato de código claro e legível no corpo da função.

Corrigir função de demonstração

A função na pergunta está quebrada . O tipo de retorno é definido para retornar um registro anônimo:

RETURNS record AS

Mas a consulta na verdade retorna um conjunto de registros, teria que ser:

RETURNS SETOF record AS

Mas isso ainda é inútil. Você teria que fornecer uma lista de definição de coluna com cada chamada. Sua consulta retorna colunas de tipo conhecido. Declare o tipo de retorno de acordo! Estou supondo aqui, use tipos de dados reais de colunas/expressões retornadas:

RETURNS TABLE (make text, model text, year int, euro_price int, colour text) AS

Eu uso os mesmos nomes de coluna por conveniência. As colunas na RETURNS TABLEcláusula são efetivamente OUTparâmetros, visíveis em todas as instruções SQL no corpo (mas não dentro EXECUTEde ). Portanto, qualifique colunas em consultas no corpo da função para evitar possíveis conflitos de nomenclatura. A função de demonstração funcionaria assim:

CREATE or REPLACE FUNCTION get_car_list (
    _colour            text, 
    _min_price         numeric, 
    _max_price         numeric, 
    _sorting_criterium car_sorting_criteria) 
  RETURNS TABLE (make text, model text, year int, euro_price numeric, colour text) AS
$func$
      SELECT c.make, c.model, c.year, c.euro_price, c.colour
      FROM   cars c
      WHERE  c.euro_price BETWEEN _min_price AND _max_price
      AND    c.colour = _colour
      ORDER  BY CASE WHEN _sorting_criterium = 'colour' THEN c.colour     END
              , CASE WHEN _sorting_criterium = 'price'  THEN c.euro_price END;
$func$  LANGUAGE sql;

Não confunda a RETURNSpalavra-chave na declaração da função com o RETURNcomando plpgsql como Evan fez em sua resposta . Detalhes:

• Diferença entre return next e return record

Dificuldade geral da consulta de exemplo

Predicado em algumas colunas (ainda pior: predicados de intervalo ), outras colunas em ORDER BY, isso já é difícil de otimizar. Mas você mencionou em um comentário :

Os conjuntos de resultados reais podem estar na ordem de várias linhas 1.000 (e, portanto, classificados e paginados em partes menores do lado do servidor)

Portanto, você adicionará LIMITe OFFSETa essas consultas, retornando as n "melhores" correspondências primeiro. Ou alguma técnica de paginação mais inteligente:

• Melhore o desempenho da ordem com colunas de várias tabelas

Você precisa de um índice correspondente para tornar isso rápido. Não vejo como isso poderia funcionar com CASEexpressões em ORDER BY.

Considerar:

• Otimize uma consulta com LIMIT pequeno, predicado em uma coluna e ordenado por outra
• O índice espacial pode ajudar uma consulta "range - order by - limit"

Não faz sentido ter um caso na ordem de uma função no caso descrito

Os índices não serão usados ​​na cláusula order by dessa forma. O mecanismo de banco de dados terá que calcular a expressão de caso para cada linha e depois classificar. E essa técnica não é extensível para junções e filtros dinâmicos.

Eu iria com uma geração dinâmica de SQL...

Gerando SQL dinâmico em uma função PostgreSQL

Você pode fazer algo assim:

  create or replace function fn_test(car_sort_option) returns text as $$
  declare
      xsql text;
      xresult text;
  begin

    xsql = 'select array_to_json(array_agg(c)) from cars';

    if $1 = 'colour' then 
      xsql = xsql || ' order by colour';
    elsif $1 = 'model' then  
      xsql = xsql || ' order by model';
    else 
      raise exception 'invalid parameter: sort option=% is invalid', $1; 
    end if;

    execute xsql into xresult;

    return xresult;
  end

  $$ language 'plpgsql';

Não costumo fazer SQL dinâmico para algo tão específico da camada de apresentação dentro do PL/pgSQL. Normalmente prefiro deixar essa construção SQL em PHP ou Java. Mas para muitas outras coisas eu faço muito SQL dinâmico dentro do PL/pgSQL. Principalmente para particionamento, manutenção de banco de dados, implementação de fluxo de trabalho e controles de consistência de dados.

Eu descobri que esta política deixa o código mais limpo, tem melhor uso de índices e é utilizável em SQL dinâmico mais complicado. O uso do índice é crítico no meu mundo porque trabalho com análises em bancos de dados com vários bilhões de registros e preciso de respostas rápidas.

Notas adicionais sobre por que é mais comum gerar SQL dinâmico fora do banco de dados (a seguir comentários)

Isso acontece porque a maioria do SQL dinâmico só é necessário quando um nome de tabela ou um nome de coluna é dinâmico. Para todo o resto, as consultas parametrizadas funcionarão bem. O SQL dinâmico é necessário para a maioria dos relatórios. Não apenas porque você precisará escolher a coluna de ordem de classificação, mas na maioria das vezes você precisará incluir filtros e colunas dinamicamente. Muitos desenvolvedores continuam relatando SQL nos programas de relatório na camada de apresentação ou nos lotes que os geram, caso em que o SQL dinâmico também será gerado fora do banco de dados.

Eu usaria SQL dinâmico neste caso.
A consulta dinâmica é menos complexa e pode render um melhor plano de execução.
Pela minha experiência, qualquer ganho com o armazenamento em cache do plano ou tempo de análise mais curto é insignificante perto disso.

Eu costumava seguir a regra de "Usar SQL dinâmico somente se o SQL estático não funcionar". Hoje, como regra geral, costumo escolhê-lo de acordo com a cláusula da consulta SQL em que preciso de flexibilidade:

• ONDE- SQL estático
• FROM, ORDER BY, GROUP BY - SQL dinâmico
• SELECIONE - Alternativo

É verdade que é um pouco mais difícil de depurar, mas o uso de algumas práticas recomendadas deve ajudar a reduzir a lacuna.
Por exemplo, em plpgsql você pode usar uma notação $ + REPLACE + RAISE NOTICE:

-- Write the entire query using $ for replacements.
-- Don't use || operator.
-- This makes the dynamic query clearer to read and easier to maintain and debug.
v_sql:= 'SELECT <some columns> FROM tbl ORDER BY $sorting$';

v_sql:= REPLACE(v_sql,
  '$sorting$',
  CASE condition
    WHEN value1 THEN 'colX'
    WHEN value2 THEN 'colY'
  END);

-- Debug the query when running
RAISE NOTICE '%', v_sql;
RETURN QUERY EXECUTE v_sql;  

Você deve ser capaz de interceptar quaisquer erros de sintaxe nas primeiras execuções.
Erros lógicos serão tão fáceis/difíceis de encontrar quanto com SQL estático.