The following bug has been logged on the website:
Bug reference: 17860
Logged by: Jiangshan Liu
Email address: jiangshan.liu@tju.edu.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.
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().
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.
The documentation also mentions that
"The SETOF modifier indicates that the function will return a set of items,
rather than a single item[2]."
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. Just like in other programming languages, such as Java, when we
expect an array of length zero, we still need to create that array to make
it an entity, otherwise it will be a null value.
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.
I would greatly appreciate your thoughts and feedback on this issue. Thank
you for your attention and assistance.
Sincerely, Jiangshan Liu
[1]
https://www.postgresql.org/docs/15/plpgsql-control-structures.html#:~:text=and%20then%20a%20final%20RETURN%20command%20with%20no%20argument%20is%20used%20to%20indicate%20that%20the%20function%20has%20finished%20executing
[2]
https://www.postgresql.org/docs/15/sql-createfunction.html#:~:text=The%20SETOF%20modifier%20indicates%20that%20the%20function%20will%20return%20a%20set%20of%20items%2C%20rather%20than%20a%20single%20item.
[3]
https://www.postgresql.org/docs/15/datatype-pseudo.html#:~:text=Indicates%20that%20a%20function%20returns%20no%20value.
