Re: Order of rows in simple "select r from table_fn()"

On Tue, Feb 14, 2023 at 4:49 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> I've found that a table function with "returns table(r text)" provides a
> convenient way to write a nicely formatted report using psql that can be
> easily directed to a file with the "\o" metacommand. In general, for cases
> like this, I can't write a useful "order by r" because the values of "r"
> interleave, for example, rule-offs between sections of the report, various
> sub-headings, and actual query results. The required order is exactly the
> order in which my code produces the rows.
>

Seems safe enough to rely upon if the query is indeed: SELECT * FROM fn();
The system has to consume the output of the function call in its serial
order and has no reason to then reorder things prior to producing the final
result set. Though I'd probably still consider adding a "report line
number" column to the output for end-user usability or if they want to sort
the report and then return to the physical order.

I am curious whether a user-defined set-returning function is allowed to
specify "WITH ORDINALITY" like the built-in UNNEST function does to produce
the output row numbers external to the function body and signature.

David J.