Skip to main content
added 464 characters in body
Source Link
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

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.

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.

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.

Source Link
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

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.