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 takes a string as parameter and calls LOWER() and removes accentuation returning the new string, so I can perform case-insensitive and accent-insensitive string comparison. Similar to unaccent which I couldn't properly apply on my use case due to some encoding problems (page is UTF8 and DB is latin1, if I convert the UTF8 to latin1 solely on the back end I'll have broken UTF8 pages; well this has been solved with the function above and is off-topic).

idcidade is just a foreign key which I use to reduce the result set to less than 0.1% of the table. Hence performance isn't really an issue in this specific case.

My real question is, does calling CASE multiple times passing the exact same parameters have an impact on performance? I assume the result from CASE should be STABLE and the query will be optimized automatically then. If that's the case, I can store part of the query into a variable on my back end and build the query string using that. Otherwise, if calling CASE multiple times in the same query with the same arguments affects performance, should I create a STABLE function for it then or what's the correct approach to this? Any pointers are appreciated.
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