Date: Tue, 16 Sep 2025 18:39:22 -0400
From: Eugen Konkov <kes-kes@yandex.ru>
Message-ID: <1513108534.20250916183922@yandex.ru>
To: "David G. Johnston" <david.g.johnston@gmail.com>,
 "pgsql-docs@lists.postgresql.org" <pgsql-docs@lists.postgresql.org>
Subject: Re: The document contains false statements
In-Reply-To: 
 <CAKFQuwYj6O1F4pFcpDX9x4QdtuBjKwt6KdzUxSswi9625Bg1ug@mail.gmail.com>
References: <175779557876.861.12514976006917547591@wrigleys.postgresql.org>
  <CAKFQuwYj6O1F4pFcpDX9x4QdtuBjKwt6KdzUxSswi9625Bg1ug@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/1513108534.20250916183922%40yandex.ru>
Precedence: bulk

Hello David,

Saturday, September 13, 2025, 7:26:21 PM, you wrote:

> On Saturday, September 13, 2025, PG Doc comments form <noreply@postgresql=
.org> wrote:
> The following documentation comment has been logged on the website:

> Page: https://www.postgresql.org/docs/17/sql-createfunction.html
> Description:

>> This is effectively another way of declaring a named OUT parameter

> Actually OUT works twice slower in compare to RETURNS TABLE
> https://stackoverflow.com/q/79763947/4632019

> DB<>fiddle for [`OUT`](https://dbfiddle.uk/fz9L_wm0) and [`RETURNS
> TABLE`](https://dbfiddle.uk/uTkU1MT8) cases.

> *I hope after the fix, data centers will consume 2 times less electricity


> The statement is not false - it contains an =E2=80=9Cexcept=E2=80=9D clau=
se that
> you=E2=80=99ve ignored which makes it true for exactly this reason.  The
> fact you are comparing a set-returning function to one that doesn=E2=80=
=99t
> return a set has invalidated the test.
> The fundamental issue here is =E2=80=9Cselect (composite_func()).*=E2=80=
=9D where
> the function is not set-returning if known to be broken - the =E2=80=9C*=
=E2=80=9D
> expansion during planning results in the function being executed
> multiple times once for each output column. (I may be missing some
> nuances here as, since the inclusion of lateral joins, this almost never =
comes up anymore.)

> Non-trivial function calls should be placed in the FROM clause of a
> query; in part to ensure avoidance of this problematic behavior.
> This is not at all limited to RLS.
> In short, I don=E2=80=99t know how to improve the documentation to prevent
> people from writing bad queries of this type.  Concrete suggestions
> are welcome, but removing this sentence, or re-wording it, doesn=E2=80=99t
> seem like it would make any difference.
> David J.

Thanks for more information on this. You and Tom both pointed me to `RETURN=
S SET OF` part. I agree with Tom that documentation is not the place to tea=
ch user how to write SQL.
But I would appeal that the documentation should be meaning full. And for m=
e the part after "except" looks the same as it would be written in Arabic "=
=D8=A5=D8=B1=D8=AC=D8=A7=D8=B9 =D9=85=D8=AC=D9=85=D9=88=D8=B9=D8=A9 =D9=85=
=D9=86" (RETURNS SETOF).

The question from David: How it could be done better? is good. Let me descr=
ibe how I see it from my point of view and experience.

The documentation above highlights as RETURNS SETOF as something special. I=
 never used SETOF. I tried to google and find almost nothing in the officia=
l documentation, except these two

https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-=
RETURNING-SET
https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-=
RETURNING-TABLE=20
with just a mention that it can return 0, 1 or more rows.

And the most informative one is https://www.postgresql.org/docs/current/que=
ries-table-expressions.html#QUERIES-TABLEFUNCTIONS

The latest one my my mind should be reffered from https://www.postgresql.or=
g/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-TABLE , Eg.
Please read 7.2.1.4 Table Functions for more information.


For the original problem:
>The name of an output column in the RETURNS TABLE syntax. This is effectiv=
ely another way of declaring a named OUT parameter,
> except that RETURNS TABLE also implies RETURNS SETOF.

My proposition is to add link to 7.2.1.4 Table Functions for more informati=
on and extend "7.2.1.4 Table Functions for more information" with the infor=
mation
that "in certain circumstances RETURNS TABLE is a subject for optimizer and=
 could be inlined https://wiki.postgresql.org/wiki/Inlining_of_SQL_function=
s#Inlining_conditions_for_table_functions".
 Having this it would be clear that `RETURNS TABLE` and `RETURNS SETOF` are=
 sort of fuctions which are called Table functions.


Probably official documentation should have in future a section somewhere "=
How to optimize your queries" and links to benchmarks like this I did in my=
 question on SO:
https://pastebin.com/n3sxBxt6=20
https://dbfiddle.uk/xfy-qw75=20

Without this information and statement that these two: OUT and RETURNS TABL=
E are just taste of syntax, users will use either without knowing consequen=
ces. (like me until I benchmarked it)

--
Best regards,
Eugen Konkov=20

--=20
Best regards,
Eugen Konkov