Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux