From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jan Behrens <jbe-mlist@magnetkern.de>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Calling set-returning functions in a non-set-expecting context
In-reply-to: <20250217175148.70e8df2414f6b47a462b418c@magnetkern.de>
References: <20250217175148.70e8df2414f6b47a462b418c@magnetkern.de>
Comments: In-reply-to Jan Behrens <jbe-mlist@magnetkern.de>
	message dated "Mon, 17 Feb 2025 17:51:48 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <849266.1739813616.1@sss.pgh.pa.us>
Date: Mon, 17 Feb 2025 12:33:36 -0500
Message-ID: <849267.1739813616@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/849267.1739813616%40sss.pgh.pa.us>
Precedence: bulk

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