PostgreSQL: create two functions or add a parameter to an existing function?

Question

Suppose I have a not trivial query to return a list of places from a given name piece e.g. "Rio" would return both "Rio de Janeiro" and "Rio Negro". But in one place I would need a short version like "Rio Negro/BR" while in other I would have "Rio Negro (Gazetteer, Amazonas, Brasil)".

What is the optimal solution in PostgreSQL: create two smaller functions or one bigger?

The bigger function would be called from two different sources, each time running one of two similar SQL requests, both large.

Maybe if I split it in two different functions it will become faster?

Answer 1

Two specialized functions are typically faster than one combined function if you just have to call one of them. If you'd have to call both specialized functions, it's the other way round: then it's typically faster to execute a single combined function. It depends on your actual use case.

There might be rare corner cases, for instance if the function is called only a few times per session. Then it might turn out to be faster to reuse the same combined function for small functions with saved query plans. (PL/pgSQL functions re-use query plans within the same session. SQL functions don't). The overhead might be more expensive for two separate functions. But that's an academic exception. Related:

• Difference between language sql and language plpgsql in PostgreSQL functions
• PostgreSQL Stored Procedure Performance