PostgreSQL simple query with repeated function calls

I have PostgreSQL 9.1.1 running in a shared hosting environment and I'm running this query:

SELECT a.id,
CASE WHEN a.idpai IS NULL THEN a.nome
ELSE concat(a.nome, ' (', b.nome, ')')
END AS nome, a.idpai
FROM localidade a
LEFT OUTER JOIN localidade b ON a.idpai = b.id
WHERE a.idcidade = :idcidade AND normalizar(
    CASE WHEN a.idpai IS NULL THEN a.nome
    ELSE concat(a.nome, ' (', b.nome, ')')
    END
) LIKE normalizar(:nome)
ORDER BY CASE WHEN normalizar(
    CASE WHEN a.idpai IS NULL THEN a.nome
    ELSE concat(a.nome, ' (', b.nome, ')')
    END
) LIKE substring(normalizar(:nome) from 2) THEN 1
ELSE 2
END, a.nome, b.nome
LIMIT 15

The performance is pretty good, the query returns in less than 50ms. Though, I'm repeating this part 3 times inside the query:

CASE WHEN a.idpai IS NULL THEN a.nome
ELSE concat(a.nome, ' (', b.nome, ')')
END

I believe this reduces the maintainability of the query and is not DRY at all. What is the recommended way to handle that? Should I create a function in my schema solely for this query? Or just store it in a variable and concatenate into the query in my back end before sending it over to the db server? Or is there any better way around?

I believe what this query does isn't the main point here, but just to give some background:

The localidade table may or may not have a relation with itself through the idpai which references a "parent" register. Assuming this table format:

id | nome | idpai
 1 | foo  |
 2 | bar  |   1

So if the query placeholder :nome contains %foo%, it will return:

id | nome       | idpai
 1 | foo        |
 2 | bar (foo)  |   1

The ORDER BY clause is just to display results which the query parameter matches the beginning of the returned row's name before those who don't - SUBSTRING('%foo%' FROM 2) returns foo% -, and then the matching and not matching groups are ordered by a.nome and b.nome ASC as the SQL shows.

normalizar is a STABLE function that t

I think this part:

CASE WHEN a.idpai IS NULL THEN a.nome
ELSE concat(a.nome, ' (', b.nome, ')')
END AS nome

Can be replaced with:

CONCAT(a.nome,  ' (' || b.nome || ')') AS nome

If b.nome is NULL, then "' (' || b.nome || ')'" will also be NULL (concatenating strings using '||' with NULL values will result in NULL. CONCAT() ignore null values, so will return only a.none in this case

In other words: if b.nome is present, it will output "a.nome (b.nome)", otherwise "a.nome"

You can test it yourself, here's my sqlfiddle:

http://sqlfiddle.com/#!1/d41d8/744/0

Also, you can try to use a subselect to prevent having to repeat this CONCAT;

http://sqlfiddle.com/#!1/a9571/7/0

I haven checked if this improves performance, but it reduces the amount of repeated code and may be better maintainable

StackExchange Code Review Q#20999, answer score: 3