Using PostgreSQL functions instead of joining?

Question

I don't have a question on how to do something but if I should. Sorry if there's a more appropriate forum.

I have a function:

CREATE OR REPLACE FUNCTION get_name(user_id UUID) RETURNS TEXT AS $$
    SELECT username
    FROM   users
    WHERE  users.id=user_id
$$ LANGUAGE 'sql';

I like using this function to translate from user_id to username in other queries. It's a common operation and I thought a function would speed up development time in looking up one-off stats for data analysis purposes. I am not concerned about query speeds. Is this good practice to use a function to perform this operation or should I just perform a join?

I am new to this and my coworker said it was bad practice to use functions for things that regular selects/ joins can perform. He also mentioned that functions should preferably be used for things that regular selects just can't do. So I wanted to ask other people what good practices are?

Edit: If this is a bad practice, would it be more reasonable for the case when the function does a common operation that's a little more complicated?

Answer 1

A function is never going to speed up what SQL already does. In fact, if you don't actually add IMMUTABLE to it, it's likely to slow it down.

I would personally never write such a trite function.

• If you're returning one row, I would use a correlated subquery.
• If you're needing multiple users, I would do the join.
• I would look probably retrieve this kind of information upon auth, like in the initial response, and send it back to the client to handle.

But above and beyond all of that, it's not merely about complexity for your function. You have a DSL that explicitly queries your schema and was created for just that purpose -- namely, SQL. What are you going to do when those functions need more logic and complexity. Been there seen that, the next step is CASE statements in functions. And, then slowly but surely you recreate a dynamic query generation mechanism. It's easier to just dynamically generate the SQL when needed.

Answer 2

There are various scenarios where functions can speed up query performance. Most notably, PL/pgSQL functions handle SQL statements like prepared statements. To be precise it uses the SPI managers SPI_prepare:

The prepared statement also provides a place for caching an execution plan if it is found that generating a custom plan for each execution is not helpful.

The manual on PREPARE has guidelines:

Prepared statements potentially have the largest performance advantage when a single session is being used to execute a large number of similar statements. The performance difference will be particularly significant if the statements are complex to plan or rewrite, e.g. if the query involves a join of many tables or requires the application of several rules. If the statement is relatively simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared statements will be less noticeable.

That should also say something to your question about "a common operation that's a little more complicated".

Obviously, you can also achieve these benefits with prepared statements. But prepared statements are limited to a single DML statement. Functions can do a lot more.

But be wary of nesting functions (that cannot be inlined) in queries. Since those are planned separately (posing as optimization barrier). That might stand in the way of finding the most efficient query plan. Like in your example, if you nest the function in a query that looks up many user names at once, that's going to be a lot more expensive than a join to the lookup table.

Only declare functions IMMUTABLE that are actually immutable. Exceptions apply, like when you need to build an expression index (and know what you are doing). Example:

• Does PostgreSQL support “accent insensitive” collations?

Else, you can actually harm performance with an incorrect volatility declaration, as it can prohibit function inlining. Quoting the Postgres Wiki on inlining:

if the function is declared IMMUTABLE, then the expression must not invoke any non-immutable function or operator

Related:

• When to use stored procedure / user-defined function?
• What is the difference between a prepared statement and a SQL or PL/pgSQL function, in terms of their purposes?
• Why can PL/pgSQL functions have side effect, while SQL functions can't?

That's merely a corrective to the currently accepted answer. But you are not primarily concerned with performance, rather with "good style". And that very much depends on the use case.

If you have a single app working with a your DB, then it's mostly a decision of language skill and taste where to encapsulate logic - in the app or in the DB. But if you have several apps working with the same DB, then it can make a lot of sense to encapsulate shared logic in the DB (with server-side functions or other means of SQL like table constraints, VIEWs, etc.) Even a trivial function like the one you display can make sense then.