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

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

david.g.johnston@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx 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.

Thanks, David. Thanks, too, to pavel.stehule@xxxxxxxxx for your separate reply that also says that I can rely on seeing the order in which I produce the rows in the function's implementation. And yes, I realize that Postgres table functions are not pipelined in the way that they can be, if you choose this, in Oracle Database.

Given that the order is pre-calculated, it seems that "with ordinality" can add line numbering "after the fact" reliably and with minimum clutter when it's needed. I tried these two variants:

create function f1()
  returns setof text
  language sql
as $body$
  values ('skiing'), ('cycling'), ('running');
$body$;
and:

create function f2()
  returns table(r text)
  language plpgsql
as $body$
begin
  r := 'skiing'; return next;
  r := 'cycling'; return next;
  r := 'running'; return next;
end;
$body$;

select t.line_no, t.report_text
from f1() with ordinality as t(report_text, line_no);

Each supports this same query

select t.line_no, t.report_text
from fN() with ordinality as t(report_text, line_no);

and gets this same result:

 line_no | report_text 
---------+-------------
       1 | skiing
       2 | cycling
       3 | running