> david.g.johns...@gmail.com wrote:
>
>> b...@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.
Thanks, David. Thanks, too, to pavel.steh...@gmail.com 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
