Thread: Calling set-returning functions in a non-set-expecting context

Hi,

I wonder if it is guaranteed that when calling a set-returning function
in a non-set-expecting context, the used row is guaranteed to be the
first row returned.

I.e. if I have the following function definition

CREATE FUNCTION foo() RETURNS INTEGER RETURN generate_series(1, 10);

is it then guaranteed, that foo() always returns 1? And if so, is that
documented somewhere? I didn't find it.

I know that generate_series creates an ordered result, so that's not my
concern, but I'm not sure whether the first row will be picked. There
is something written here:

https://www.postgresql.org/docs/17/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

"SQL functions execute an arbitrary list of SQL statements, returning
the result of the last query in the list. In the simple (non-set) case,
the first row of the last query's result will be returned. (Bear in
mind that 'the first row' of a multirow result is not well-defined
unless you use ORDER BY.) If the last query happens to return no rows
at all, the null value will be returned."

But this part explicitly mentions queries. Using the "RETURN"
statement, I don't give a query but an expression. So does the "first
row gets used" rule also apply in my context, and why so?

For example, the following command results in an error:

SELECT (SELECT generate_series(1, 10));
ERROR:  more than one row returned by a subquery used as an expression

Kind regards and thanks for your advice,
Jan Behrens

Jan Behrens <jbe-mlist@magnetkern.de> writes:
> I wonder if it is guaranteed that when calling a set-returning function
> in a non-set-expecting context, the used row is guaranteed to be the
> first row returned.

In general, I'd expect either we'd use the first row or throw an
error.  We're not 100% consistent about which rule applies, but
I can't think of a reason for anything to do something else.

> I.e. if I have the following function definition
> CREATE FUNCTION foo() RETURNS INTEGER RETURN generate_series(1, 10);
> is it then guaranteed, that foo() always returns 1? And if so, is that
> documented somewhere? I didn't find it.

I'd say that using the first row (and not throwing an error) is
guaranteed for the specific case of SQL-language functions by the same
text you quote:

> "SQL functions execute an arbitrary list of SQL statements, returning
> the result of the last query in the list. In the simple (non-set) case,
> the first row of the last query's result will be returned. (Bear in
> mind that 'the first row' of a multirow result is not well-defined
> unless you use ORDER BY.) If the last query happens to return no rows
> at all, the null value will be returned."

The reason for the parenthetical weasel-wording is that a query might
require joins, aggregation, etc, and some of our implementations of
those things don't preserve row order.  If we're talking about a
simple invocation of another set-returning function, it's just
going to take whatever that function returns first.

            regards, tom lane