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

From: Jan Behrens <jbe-mlist(at)magnetkern(dot)de>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Calling set-returning functions in a non-set-expecting context
Date: 2025-02-17 16:51:48
Message-ID: 20250217175148.70e8df2414f6b47a462b418c@magnetkern.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2025-02-17 17:33:36 Re: Calling set-returning functions in a non-set-expecting context
Previous Message Adrian Klaver 2025-02-17 16:10:17 Re: The performance issues caused by upgrading PostgreSQL to version 16.3.