In general, the order of the rows in a result set is stated to be unpredictable
without an "order by" at the outermost level. Famously, beginners observe what
seems to be reliably reproducible ordering in some queries that don't have an
"order by"—and it can take some effort to persuade them that they cannot rely
on what seems to be a reliable order unless they clutter the SQL (and add the
cost of sorting) by adding an "order by" clause.
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.
Here's a trivial, artificial, example:
create function report_1()
returns table(r text)
language plpgsql
as $body$
declare
rule constant text not null := lpad('—', 40, '—');
begin
r := rule; return next;
r := 'MY REPORT'; return next;
r := rule; return next;
r := ''; return next;
r := 'Section 1'; return next;
r := '---------'; return next;
for r in (
select v::text
from t1
order by v
) loop
/**/ return next;
end loop;
r := ''; return next;
r := 'Section 2'; return next;
r := '---------'; return next;
for r in (
select v::text
from t2
order by v desc
) loop
/**/ return next;
end loop;
r := rule; return next;
end;
$body$;
And this is the output, given some suitable content in t1 and t2, from "select
r from report_1()" with no "order by":
————————————————————————————————————————
MY REPORT
————————————————————————————————————————
Section 1
---------
10
12
14
16
Section 2
---------
27
24
21
————————————————————————————————————————
I've written no end of reports this way. And I've never, ever, seen the rows
come out in an order that differs from the order in which they're written. (Of
course, I know that this proves nothing.) Here's a variant that lets me say
"select r from report_1() order by k":
create function report_2()
returns table(k int, r text)
language plpgsql
as $body$
declare
rule constant text not null := lpad('—', 40, '—');
begin
k = 1; r := rule; return next;
k = k + 1; r := 'MY REPORT'; return next;
k = k + 1; r := rule; return next;
k = k + 1; r := ''; return next;
k = k + 1; r := 'Section 1'; return next;
k = k + 1; r := '---------'; return next;
for r in (
select v::text
from t1
order by v
) loop
k = k + 1; return next;
end loop;
k = k + 1; r := ''; return next;
k = k + 1; r := 'Section 2'; return next;
k = k + 1; r := '---------'; return next;
for r in (
select v::text
from t2
order by v desc
) loop
k = k + 1; return next;
end loop;
k = k + 1; r := rule; return next;
end;
$body$;
It adds an uncomfortable amount of clutter.
* Is it essential for correctness? *
It's annoying that the use of "return next" prevents the pattern that each
"print line" follows from being encapsulated into a procedure. But it is what
it is, yes?
