Re: BUG #17860: Possible Bugs in PL/pgSQL Functions' Return Values

On Wed, Mar 22, 2023 at 6:15 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17860
> Logged by: Jiangshan Liu
> Email address: jiangshan(dot)liu(at)tju(dot)edu(dot)cn
> PostgreSQL version: 15.2
> Operating system: Ubuntu 18.04.6 LTS
> Description:
>
> I am writing to report a possible bug that I have encountered with the
> execution of two PL/pgSQL functions. The results that they produce have
> left
> me confused and I would appreciate your insights and assistance with this
> matter.
>

In short - a set producing function creates an explicit table that may
contain zero, one, or many rows. A non-set producing function creates a
single value, always, which if you happen to place it in a place that wants
a table reference will result in there always being a single row table.
But typically one does not place non-set producing functions in a FROM
clause.

> The first function is:
> CREATE OR REPLACE FUNCTION test1() RETURNS SETOF BOOLEAN AS $$
> BEGIN
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
> select count(*) from test1();
>
> The result of executing this function is:
> count
> -------
> 0
> (1 row)
>
> The second function is:
> CREATE OR REPLACE FUNCTION test2() RETURNS VOID AS $$
> BEGIN
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
> select count(*) from test2();
>
> The result of executing this function is:
> count
> -------
> 1
> (1 row)
>
> At the same time, I discovered that the returned 1 row data result is of
> type "void":
> select pg_typeof(test2());
> pg_typeof
> -----------
> void
> (1 row)
>
> I believe that there is a confusing bug present here. For the function
> test1(), according to the documentation,
> "The individual items to return are specified by a sequence of RETURN NEXT
> or RETURN QUERY commands, and then a final RETURN command with no argument
> is used to indicate that the function has finished executing[1]."
>
> Since there are no actual RETURN NEXT or RETURN QUERY executed, the
> behavior
> should be equivalent to a RETURN command with no argument, similar to the
> function test2().
>

This is an incorrect conclusion.

> However, the results of test1() and test2() are different. I am unsure if
> the empty table returned by test1() is a deliberate design or not, but it
> does not seem to align with the intention of SETOF return values.
>

A table, empty or not, is exactly what "SET" means in SQL.

>
> The documentation also mentions that
> "The SETOF modifier indicates that the function will return a set of items,
> rather than a single item[2]."
>

Sets can be empty, we need not make that point explicitly whenever we use
the word.

>
> Regardless of whether the length of the returned set is zero or non-zero,
> we
> expect to receive a single entity as a return value, rather than an empty
> table.

Your "single entity" is a table-like structure of which there is indeed
exactly one. And when you count the number of rows in the table-like (set)
structure you correctly count zero rows.

>
> Moreover, regarding function test2(), the result is also perplexing.
> According to the documentation,
> "Data type void Indicates that a function returns no value."
>
> Therefore, it is reasonable to expect that the returned value should be an
> empty table, as it should not be returning any value. However, in reality,
> it returns a table with one row.
>

A non-SET returning function doesn't return a table-like structure, it
returns a value (one row, one column - possibly composite). If you place
that value into a FROM clause it looks like a single row table but that is
just a detail of how SQL generally works when it tries to accommodate your
oddly written query where a non-table producing function is used in a FROM
clause.

The system retains the data type "void" for that singular value as the
representation of the fact that the function that was called did not itself
return any particular value.

David J.