2

There's a blog for SQL Server that says returning a scalar using SELECT is wasteful:

SELECT should be used for returning resultsets, not scalars. This procedure uses a SELECT statement to return a single value to the client. This is inefficient because most applications will have to prepare additional objects (typically referred to as "recordsets") and other support in order to consume the result. While it is certainly valid syntax to use SELECT to return scalar values, this does not need to be common in production code. This is the kind of thing that can make it slightly harder for high-end applications to scale.

Does this apply to PostgreSQL as well? Should functions always use RETURN for outputting scalars, or is it ok to use SELECT whenever it's more convenient?

4

I'd prefer to use RETURN rather than RETURN QUERY SELECT in pl/pgsql.

But there's no need to jump through hoops. The cost of returning results with SELECT is negligible compared to other costs in pl/pgsql procedures.

Everything is a result-set in PostgreSQL anyway, even a single scalar, so you're not saving much with RETURN.

|improve this answer|||||
1

None of this seems applicable to PostgreSQL.

In PostgresSQL, you can write your functions in SQL, or in pretty much any procedural language with bindings, including plpgsql, perl, python, and javascript/v8. When you say "return with SELECT," I read that to mean, as a SQL function -- the only kind that can implicitly return with SELECT. Using SQL for your scalar functions is great, and with SELECT it may be even faster because it may be inlined entirely.

CREATE FUNCTION foo()
RETURNS bigint
AS $$
  SELECT count(*) FROM bar;
$$ LANGUAGE sql;

That said, the above won't be because it contains an aggregate. =(

If you're using pl/pgsql, see Craig's answer.

|improve this answer|||||
  • 1
    The language name is an identifier. Do not put it between single quotes. – a_horse_with_no_name Oct 17 '17 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.