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?
Best Answer
I'd prefer to use
RETURN
rather thanRETURN 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
.