On Tue, Feb 14, 2023 at 4:49 PM Bryn Llewellyn <bryn@yugabyte.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.