Uma técnica de concatenação para SQL

Na tabela users de uma base de dados do Redmine existe um campo firstname e outro lastname e eu precisava mostrar ambos concatenados, separados por um espaço, representando assim um nome completo. O problema? Bem, quando o usuário na verdade é um grupo, firstname contem '' (vazio) no lugar de NULL, e isto muda toda a história.

Então imagine os seguintes registros:

Na listagem acima, vemos que Cliente, Desenvolvedor e Suporte não tem um firstname, isto porque o tipo deles é Group.

O resultado esperado que eu quero é este:

Vamos a consulta mais óbvia:

SELECT firstname||' '||lastname as "Autor" FROM users;

A fim de que vocês consigam visualizar um “espaço” sobrando, a saida está formatada em texto, no lugar de HTML:

| Autor          |
|----------------|
| Joao Pedro     |
| Maria Silva    |
| Antonio Carlos |
|  Cliente       |
|  Desenvolvedor |
|  Suporte       |
|                |

Se firstname fosse NULL no lugar de '' para estes casos, o SELECT abaixo já seria suficiente:

SELECT COALESCE(firstname || ' ', '') || lastname FROM users;

Em outras palavras, concatene firstname com um espaço, se o resultado disto for NULL então retorne vazio '' e ai então concatene com lastname.

Como nem tudo são flores é óbvio que precisamos ter a opção “mais difícil”, então teriamos que primeiro “inverter” o vazio para NULL e ai usar a técnica acima, conforme:

SELECT COALESCE(NULLIF(firstname, '') || ' ', '') || FROM users;

Ou seja, verifique se firstname é vazio (NULLIF(firstname, '')) e retorne NULL caso positivo, concatene o resultado disto com um espaço, se o resultado disto for NULL então retorne vazio ='' e ai então concatene com lastname.

Ufa!

O problema acabou por aqui? Claro que não, foi necessário acresentar o email que tem o mesmo comportamento que o firstname, retornando vazio em alguns casos e a solução com o COALESCE + NULLIF seria um macarrônico. Daria para resolver com CASE ... WHEN mas seria uma solução mais imperativa, do que declarativa.

Foi então resolvi utilizar uma outra abordagem:

SELECT array_to_string(array[ nullif(firstname, ''), lastname, nullif(email, '') ], ' ') as "Autor"
FROM users;

Neste caso eu monto um array contendo firtname ou NULL, lastname, email ou NULL e então chamo array_to_string passando este array como parâmetro e o separador ' ' (espaço). Esta solução me permite evitar aninhamentos de COALESCE e NULLIF, e parece mais declarativa que a solução anterior.

A função array_to_string ainda permite passar um terceiro argumento, que é o valor padrão que ela preenche no lugar dos valores NULL, o que daria para colocar algo como Desconhecido, mas não se aplicaria a solução aqui demonstrada que é justamente ignorar e não ter qualquer texto ou espaço “sobrando”.

Obrigado por ler até aqui. “:)