Using PostgreSQL functions instead of joining?

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?

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.

StackExchange Database Administrators Q#209795, answer score: 6