public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Jan Behrens <[email protected]>
Cc: [email protected]
Subject: Re: Calling set-returning functions in a non-set-expecting context
Date: Mon, 17 Feb 2025 12:33:36 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

Jan Behrens <[email protected]> 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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Calling set-returning functions in a non-set-expecting context
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox